CREATE PROCEDURE search_orders_4
                 @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

   -- Single order, use fixed plan.
   IF @orderid IS NOT NULL
   BEGIN
      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 = @orderid
        AND  od.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)
      ORDER  BY o.OrderID
   END
   -- A certain customer's recent orders, use fixed plan.
   ELSE IF @custid IS NOT NULL AND
        datediff(DAY, getdate(), @fromdate) <= 7
   BEGIN
      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.OrderDate >= @fromdate
        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  c.CustomerID = @custid
        AND  o.CustomerID = @custid
        AND  (od.ProductID = @prodid OR @prodid IS NULL)
        AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
      ORDER  BY o.OrderID
   END
   ELSE
   -- Everything else, recompile each time.
   BEGIN
      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.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.CompanyName 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