USE Northgale
go
CREATE PROCEDURE search_orders_2
                 @orderid     int          = NULL,
                 @status      char(1)      = NULL,
                 @fromdate    date         = NULL,
                 @todate      date         = NULL,
                 @minprice    money        = NULL,
                 @maxprice    money        = 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

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 = coalesce(@orderid, o.OrderID)
  AND  o.Status = coalesce(@status, o.Status)
  AND  o.OrderDate >= coalesce(@fromdate, o.OrderDate)
  AND  o.OrderDate <= coalesce(@todate, o.OrderDate)
  AND  od.UnitPrice >= coalesce(@minprice, od.UnitPrice)
  AND  od.UnitPrice <= coalesce(@maxprice, od.UnitPrice)
  AND  o.CustomerID = coalesce(@custid, o.CustomerID)
  AND  c.CustomerName LIKE coalesce(@custname, '') + '%'
  AND  c.City = coalesce(@city, c.City)
  AND  c.Region = coalesce(@region, c.Region)
  AND  c.Country = coalesce(@country, c.Country)
  AND  od.ProductID = coalesce(@prodid, od.ProductID)
  AND  p.ProductName LIKE coalesce(@prodname, '') + '%'
ORDER  BY o.OrderID
OPTION (RECOMPILE)