USE Northgale
go
CREATE PROCEDURE search_orders_5
                 @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,
                 @sortcol1    varchar(20)   = 'OrderID',
                 @isdesc1     bit           = 0,
                 @sortcol2    varchar(20)   = 'ProductID',
                 @isdesc2     bit           = 0 AS

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   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.Status = @status OR @status IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.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  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY
   CASE WHEN @isdesc1 = 0 THEN
           CASE @sortcol1 WHEN 'OrderID'    THEN o.OrderID
                          WHEN 'EmployeeID' THEN o.EmployeeID
                          WHEN 'ProductID'  THEN od.ProductID
           END
   END  ASC,
   CASE WHEN @isdesc1 = 1 THEN
           CASE @sortcol1 WHEN 'OrderID'    THEN o.OrderID
                          WHEN 'EmployeeID' THEN o.EmployeeID
                          WHEN 'ProductID'  THEN od.ProductID
           END
   END  DESC,
   CASE WHEN @isdesc1 = 0 THEN
         CASE @sortcol1 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END ASC,
   CASE WHEN @isdesc1 = 1 THEN
         CASE @sortcol1 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END DESC,
   CASE WHEN @isdesc1 = 0 AND @sortcol1 = 'OrderDate' THEN o.OrderDate
   END ASC,
   CASE WHEN @isdesc1 = 1 AND @sortcol1 = 'OrderDate' THEN o.OrderDate
   END DESC,
   CASE WHEN @isdesc2 = 0 THEN
           CASE @sortcol2 WHEN 'OrderID'    THEN o.OrderID
                          WHEN 'EmployeeID' THEN o.EmployeeID
                          WHEN 'ProductID'  THEN od.ProductID
           END
   END  ASC,
   CASE WHEN @isdesc2 = 1 THEN
           CASE @sortcol2 WHEN 'OrderID'    THEN o.OrderID
                          WHEN 'EmployeeID' THEN o.EmployeeID
                          WHEN 'ProductID'  THEN od.ProductID
           END
   END  DESC,
   CASE WHEN @isdesc2 = 0 THEN
         CASE @sortcol2 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END ASC,
   CASE WHEN @isdesc2 = 1 THEN
         CASE @sortcol2 WHEN 'CustomerName' THEN c.CustomerName
                        WHEN 'ProductName'  THEN p.ProductName
         END
   END DESC,
   CASE WHEN @isdesc2 = 0 AND @sortcol2 = 'OrderDate' THEN o.OrderDate
   END ASC,
   CASE WHEN @isdesc2 = 1 AND @sortcol2 = 'OrderDate' THEN o.OrderDate
   END DESC
OPTION (RECOMPILE)
go