USE Northgale
go
CREATE PROCEDURE search_orders_25
                 @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, 
                 @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' + @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 
BEGIN
   SELECT @sql = ' CREATE TABLE #innertemp (empid int NOT NULL PRIMARY KEY)
                   INSERT #innertemp (empid) SELECT n FROM dbo.intlist_to_tbl(@employeestr)' + @nl + @sql
   SELECT @sql += ' AND o.EmployeeID IN (SELECT empid FROM #innertemp)' + @nl
END
 
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)'
 
EXEC sp_executesql @sql, @paramlist, 
                   @orderid, @status, @fromdate, @todate, @minprice, 
                   @maxprice,  @custid, @custname, @city, @region, 
                   @country, @prodid, @prodname, @employeestr