USE Northgale go CREATE PROCEDURE search_orders_22 @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, @employeestr varchar(MAX) = NULL, @employeetbl intlist_tbltype READONLY, @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.Orders o JOIN dbo.[Order Details] 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' + CASE WHEN @status <> 'C' THEN ' AND o.Status <> ''C''' ELSE '' END + @nl IF @fromdate = @todate SELECT @sql += ' AND o.OrderDate = @fromdate' + @nl ELSE BEGIN IF @fromdate IS NOT NULL SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl IF @todate IS NOT NULL SELECT @sql += ' AND o.OrderDate <= @todate' + @nl END 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 SELECT @sql += ' ORDER BY o.OrderID' + @nl 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' EXEC sp_executesql @sql, @paramlist, @orderid, @status, @fromdate, @todate, @minprice, @maxprice, @custid, @custname, @city, @region, @country, @prodid, @prodname, @employeestr, @employeetbl