USE Northgale
go
CREATE PROCEDURE search_orders_23 
                 @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' + @nl 
 
IF @fromdate = @todate
   SELECT @sql += ' AND o.OrderDate = @fromdate' + @nl
ELSE
BEGIN
   IF @fromdate IS NOT NULL AND @todate IS NOT NULL
   BEGIN
      DECLARE @days int = datediff(DAY, @fromdate, @todate) + 1
      SELECT @sql += ' -- ' + CASE WHEN @days < 7 
                                        THEN convert(varchar, @days) + ' days.'
                                   WHEN @days < 35 
                                        THEN convert(varchar, @days / 7) + ' weeks.'
                                   ELSE convert(varchar, @days / 30) + ' months.'
                              END + @nl
   END                             
   
   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 = ' + 
                   CASE @custid WHEN 'ERNTC' THEN '''ERNTC''' ELSE '@custid' END + @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