USE Northgale
go
CREATE PROCEDURE search_orders_7
                 @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,
                 @ishistoric  bit           = 0 AS

DECLARE @hasemptbl bit = CASE WHEN EXISTS (SELECT * FROM @employeetbl)
                              THEN 1
                              ELSE 0
                         END

SELECT u.OrderID, u.OrderDate, u.EmployeeID, u.Status,
       c.CustomerID, c.CustomerName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, u.UnitPrice, u.Quantity,
       p.ProductID, p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM  (SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.Status, o.CustomerID,
              od.UnitPrice, od.Quantity, od.ProductID
       FROM   Orders o
       JOIN   [Order Details] od ON o.OrderID = od.OrderID
       WHERE  @ishistoric = 0
       UNION  ALL
       SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.Status, o.CustomerID,
              od.UnitPrice, od.Quantity, od.ProductID
       FROM   HistoricOrders o
       JOIN   HistoricOrderDetails od ON o.OrderID = od.OrderID
       WHERE  @ishistoric = 1) AS u
JOIN   Customers c ON c.CustomerID = u.CustomerID
JOIN   Products p ON p.ProductID = u.ProductID
WHERE  (u.OrderID = @orderid OR @orderid IS NULL)
  AND  (u.Status = @status OR @status IS NULL)
  AND  (u.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (u.OrderDate <= @todate OR @todate IS NULL)
  AND  (u.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (u.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (u.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CustomerName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (c.Country = @country OR @country IS NULL)
  AND  (u.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
  AND  (u.EmployeeID IN (SELECT n FROM intlist_to_tbl(@employeestr)) OR
        @employeestr IS NULL)
  AND  (u.EmployeeID IN (SELECT val FROM @employeetbl) OR @hasemptbl = 0)
ORDER  BY u.OrderID
OPTION (RECOMPILE)