USE Northgale
go
CREATE PROCEDURE search_orders_24 
                 @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, 
                 @city_bad    nvarchar(25)  = NULL, 
                 @region      nvarchar(15)  = NULL, 
                 @country     nvarchar(15)  = NULL, 
                 @prodid      int           = NULL, 
                 @prodname    nvarchar(40)  = NULL, 
                 @employeestr varchar(MAX)  = NULL, 
                 @employeebad 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 = ' + convert(varchar(10), @orderid) + @nl
 
IF @status IS NOT NULL 
   SELECT @sql += ' AND o.Status = ' + quotename(@status, '''') + @nl 
 
IF @fromdate IS NOT NULL 
   SELECT @sql += ' AND o.OrderDate >= ' + quotename(convert(char(8), @fromdate, 112), '''') + @nl 
 
IF @todate IS NOT NULL 
   SELECT @sql += ' AND o.OrderDate <= ' + quotename(convert(char(8), @todate, 112), '''')  + @nl 
 
IF @minprice IS NOT NULL 
   SELECT @sql += ' AND od.UnitPrice >= ' + convert(varchar(11), @minprice)  + @nl 
 
IF @maxprice IS NOT NULL 
   SELECT @sql += ' AND od.UnitPrice <= ' + convert(varchar(11), @maxprice)  + @nl 
 
IF @custid IS NOT NULL 
   SELECT @sql += ' AND o.CustomerID = N' + quotename(@custid, '''') + @nl 
 
IF @custname IS NOT NULL 
   SELECT @sql += ' AND c.CustomerName LIKE N' + quotename(@custname, '''') + ' + ''%''' + @nl 
 
IF @city IS NOT NULL 
   SELECT @sql += ' AND c.City = N' + quotename(@city, '''') + @nl
 
IF @city_bad IS NOT NULL 
   SELECT @sql += ' AND c.City = ''' + @city_bad + ''''

IF @region IS NOT NULL 
   SELECT @sql += ' AND c.Region = N' + quotename(@region, '''') + @nl 
 
IF @country IS NOT NULL 
   SELECT @sql += ' AND c.Country = N' + quotename(@country, '''') + @nl 
 
IF @prodid IS NOT NULL 
   SELECT @sql += ' AND od.ProductID = ' + convert(varchar(10), @prodid) + @nl 
 
IF @prodname IS NOT NULL 
   SELECT @sql += ' AND p.ProductName LIKE N' + quotename(@prodname, '''') + ' + ''%''' + @nl 
 
IF @employeestr IS NOT NULL 
BEGIN
   CREATE TABLE #emps (empid int NOT NULL PRIMARY KEY)
   INSERT #emps(empid) 
      SELECT n FROM dbo.intlist_to_tbl(@employeestr)
   IF @@rowcount < 64
   BEGIN
      SELECT @employeestr =     -- On SQL 2017: string_agg(convert(varchar(10), empid), ',') FROM #emps
         (SELECT convert(varchar(10), empid) + ',' 
          FROM   #emps
          FOR XML PATH(''))
      SELECT @employeestr = substring(@employeestr, 1, len(@employeestr) - 1)
      SELECT @sql += ' AND o.EmployeeID IN (' + @employeestr + ')' + @nl 
   END
   ELSE
      SELECT @sql += ' AND o.EmployeeID IN (SELECT empid FROM #emps)' + @nl
END

IF @employeebad IS NOT NULL 
   SELECT @sql += ' AND o.EmployeeID IN (' + @employeebad + ')' + @nl 

IF EXISTS (SELECT * FROM @employeetbl)
BEGIN
   IF (SELECT COUNT(*) FROM @employeetbl) < 64
   BEGIN
      SELECT @employeestr =     -- On SQL 2017: string_agg(convert(varchar(10), empid), ',') FROM @employeetbl
         (SELECT convert(varchar(10), val) + ',' 
          FROM   @employeetbl
          FOR XML PATH(''))
      SELECT @employeestr = substring(@employeestr, 1, len(@employeestr) - 1)
      SELECT @sql += ' AND o.EmployeeID IN (' + @employeestr + ')' + @nl 
   END
   ELSE
      SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl
END

 
SELECT @sql += ' ORDER BY o.OrderID' + @nl 
 
IF @debug = 1 
   PRINT @sql 
 
EXEC sp_executesql @sql, N'@employeetbl intlist_tbltype READONLY', @employeetbl