
USE Northwind
GO

CREATE PROCEDURE search_orders_3a
                   @orderid   int          = 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, od.UnitPrice, od.Quantity,
         c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
         c.PostalCode, c.Country, c.Phone, 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.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.CompanyName 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)
  
-- defaults (all NULL)
EXEC search_orders 

-- only seek predicate is customerid
EXEC search_orders_3a @custid = 'HANAR'

-- only seek predicate is orderid
exec search_orders @orderid = 11000
GO

-- In SQL Server 2005 it scans the Orders table
-- In SQL Server 2008 you get a clustered index seek 




