USE Northgale go CREATE PROCEDURE search_orders_21 @orderid int = NULL, @status char(1) = NULL, @fromdate date = NULL, @todate date = NULL, @minprice decimal(10,2) = NULL, @maxprice decimal(10,2) = NULL, @custid nchar(5) = NULL, @custname nvarchar(40) = NULL, @city nvarchar(25) = NULL, @region nvarchar(15) = NULL, @country nvarchar(15) = NULL, @prodid int = NULL, @prodname nvarchar(40) = NULL, @suppl_country nvarchar(15) = NULL, @employeestr varchar(MAX) = NULL, @employeetbl intlist_tbltype READONLY, @ishistoric bit = 0, @sortcol1 varchar(20) = NULL, @isdesc1 bit = 0, @sortcol2 varchar(20) = NULL, @isdesc2 bit = 0, @debug bit = 0 AS DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000), @nl char(2) = char(13) + char(10) SELECT @sql = 'SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.Status, c.CustomerID, c.CustomerName, c.Address, c.City, c.Region, c.PostalCode, c.Country, c.Phone, od.UnitPrice, od.Quantity, p.ProductID, p.ProductName, p.UnitsInStock, p.UnitsOnOrder FROM dbo.' + CASE @ishistoric WHEN 0 THEN 'Orders' WHEN 1 THEN 'HistoricOrders' END + ' o JOIN dbo.' + CASE @ishistoric WHEN 0 THEN '[Order Details]' WHEN 1 THEN 'HistoricOrderDetails' END + ' od ON o.OrderID = od.OrderID JOIN dbo.Customers c ON o.CustomerID = c.CustomerID JOIN dbo.Products p ON p.ProductID = od.ProductID WHERE 1 = 1' + @nl IF @orderid IS NOT NULL SELECT @sql += ' AND o.OrderID = @orderid' + @nl IF @status IS NOT NULL SELECT @sql += ' AND o.Status = @status' + @nl IF @fromdate IS NOT NULL SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl IF @todate IS NOT NULL SELECT @sql += ' AND o.OrderDate <= @todate' + @nl IF @minprice IS NOT NULL SELECT @sql += ' AND od.UnitPrice >= @minprice' + @nl IF @maxprice IS NOT NULL SELECT @sql += ' AND od.UnitPrice <= @maxprice' + @nl IF @custid IS NOT NULL SELECT @sql += ' AND o.CustomerID = @custid' + @nl IF @custname IS NOT NULL SELECT @sql += ' AND c.CustomerName LIKE @custname + ''%''' + @nl IF @city IS NOT NULL SELECT @sql += ' AND c.City = @city' + @nl IF @region IS NOT NULL SELECT @sql += ' AND c.Region = @region' + @nl IF @country IS NOT NULL SELECT @sql += ' AND c.Country = @country' + @nl IF @prodid IS NOT NULL SELECT @sql += ' AND od.ProductID = @prodid' + @nl IF @prodname IS NOT NULL SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl IF @employeestr IS NOT NULL SELECT @sql += ' AND o.EmployeeID IN' + ' (SELECT n FROM dbo.intlist_to_tbl(@employeestr))' + @nl IF EXISTS (SELECT * FROM @employeetbl) SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl IF @suppl_country IS NOT NULL SELECT @sql += ' AND EXISTS (SELECT * FROM dbo.Suppliers s WHERE s.SupplierID = p.SupplierID AND s.Country = @suppl_country)' + @nl DECLARE @namecolmap TABLE (sortname nvarchar(30) NOT NULL PRIMARY KEY, colexpr nvarchar(100) NOT NULL) INSERT @namecolmap (sortname, colexpr) VALUES('OrderID', 'o.OrderID'), ('EmployeeID', 'o.EmployeeID'), ('ProductID', 'od.ProductID'), ('CustomerName', 'c.CustomerName'), ('ProductName', 'p.ProductName'), ('OrderDate', 'o.OrderDate') IF @sortcol1 IS NOT NULL BEGIN SELECT @sql += ' ORDER BY ' + isnull((SELECT colexpr FROM @namecolmap WHERE sortname = @sortcol1), '[Unknown sort option]') + CASE @isdesc1 WHEN 0 THEN ' ASC' ELSE ' DESC' END END IF @sortcol2 IS NOT NULL BEGIN SELECT @sql += ', ' + isnull((SELECT colexpr FROM @namecolmap WHERE sortname = @sortcol2), '[Unknown sort option]') + CASE @isdesc2 WHEN 0 THEN ' ASC' ELSE ' DESC' END END IF @debug = 1 PRINT @sql SELECT @paramlist = '@orderid int, @status char(1), @fromdate date, @todate date, @minprice decimal(10,2), @maxprice decimal(10,2), @custid nchar(5), @custname nvarchar(40), @city nvarchar(25), @region nvarchar(15), @country nvarchar(15), @prodid int, @prodname nvarchar(40), @employeestr varchar(MAX), @employeetbl dbo.intlist_tbltype READONLY, @suppl_country nvarchar(15)' EXEC sp_executesql @sql, @paramlist, @orderid, @status, @fromdate, @todate, @minprice, @maxprice, @custid, @custname, @city, @region, @country, @prodid, @prodname, @employeestr, @employeetbl, @suppl_country