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

   CREATE TABLE #custids (custid nchar(5) NOT NULL)

   CREATE TABLE #products(ProductID    int          NOT NULL,
                          ProductName  nvarchar(40) NOT NULL,
                          UnitsInStock smallint     NULL,
                          UnitsOnOrder smallint     NULL)

   IF @orderid IS NULL
   BEGIN
      INSERT #custids (custid)
         SELECT CustomerID
         FROM   Customers c
         WHERE  (c.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)
   END

   INSERT #products(ProductID, ProductName, UnitsInStock, UnitsOnOrder)
      SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder
      FROM   Products
      WHERE  (ProductID = @prodid OR @prodid IS NULL)
        AND  ProductName LIKE coalesce(@prodname + '%', '%')

   EXEC search_orders_7_sub @orderid, @fromdate, @todate, @minprice, @maxprice

go
   CREATE PROCEDURE search_orders_7_sub
                    @orderid   int          = NULL,
                    @fromdate  datetime     = NULL,
                    @todate    datetime     = NULL,
                    @minprice  money        = NULL,
                    @maxprice  money        = NULL WITH RECOMPILE AS

   IF @orderid IS 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   #custids t ON o.CustomerID = t.custid
      JOIN   Customers c ON o.CustomerID = c.CustomerID
                        AND t.custid = c.CustomerID
      JOIN   #products p ON p.ProductID = od.ProductID
      WHERE  o.OrderDate >= coalesce(@fromdate, '17530101')
        AND  o.OrderDate <= coalesce(@todate, '99991231')
        AND  od.UnitPrice >= coalesce(@minprice, 0)
        AND  od.UnitPrice <= coalesce(@maxprice, 1E18)
      ORDER  BY o.OrderID
   END
   ELSE
   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 >= coalesce(@minprice, 0)
        AND  od.UnitPrice <= coalesce(@maxprice, 1E18)
   END