USE Northgale
go
CREATE PROCEDURE search_orders_9_sub1
                 @orderid     int          = NULL,
                 @status      char(1)      = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      nchar(5)     = NULL,
                 @custname    nvarchar(40) = NULL,
                 @city        nvarchar(15) = NULL,
                 @region      nvarchar(15) = NULL,
                 @country     nvarchar(15) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL 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 
     AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
     AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
     AND  (od.ProductID = @prodid OR @prodid IS NULL)
     AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
go
CREATE PROCEDURE search_orders_9_sub2
                 @orderid     int          = NULL,
                 @status      char(1)      = NULL,
                 @fromdate    datetime     = NULL,
                 @todate      datetime     = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = NULL,
                 @custid      nchar(5)     = NULL,
                 @custname    nvarchar(40) = NULL,
                 @city        nvarchar(15) = NULL,
                 @region      nvarchar(15) = NULL,
                 @country     nvarchar(15) = NULL,
                 @prodid      int          = NULL,
                 @prodname    nvarchar(40) = NULL 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.OrderDate >= @fromdate
     AND  o.CustomerID = @custid
     AND  (o.Status = @status OR @status 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  (od.ProductID = @prodid OR @prodid IS NULL)
     AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
   ORDER  BY o.OrderID
go
CREATE PROCEDURE search_orders_9
                 @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 AS

IF @orderid IS NOT NULL
BEGIN 
   EXEC search_orders_9_sub1 @orderid, @status, @fromdate, @todate, @minprice,
                             @maxprice, @custid, @custname, @city, @region,
                             @country, @prodid, @prodname
END
ELSE IF @custid IS NOT NULL AND
     datediff(DAY, @fromdate, getdate()) <= 7
BEGIN
   EXEC search_orders_9_sub2 @orderid, @status, @fromdate, @todate, @minprice,
                             @maxprice, @custid, @custname, @city, @region,
                             @country, @prodid, @prodname
END
ELSE
BEGIN
   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.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 o.OrderID
   OPTION (RECOMPILE)
END
go