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

   IF @orderid IS NOT NULL
   BEGIN
      EXEC search_orders_4a_sub1 @orderid   = @orderid,
                                 @minprice  = @minprice,
                                 @maxprice  = @maxprice,
                                 @prodid    = @prodid,
                                 @prodname  = @prodname
   END
   ELSE IF @custid IS NOT NULL
   BEGIN
      EXEC search_orders_4a_sub2 @fromdate  = @fromdate,
                                 @todate    = @todate,
                                 @minprice  = @minprice,
                                 @maxprice  = @maxprice,
                                 @custid    = @custid,
                                 @custname  = @custname,
                                 @city      = @city,
                                 @region    = @region,
                                 @country   = @country,
                                 @prodid    = @prodid,
                                 @prodname  = @prodname
   END
   ELSE
   BEGIN
      EXEC search_orders_4a_sub3 @fromdate  = @fromdate,
                                 @todate    = @todate,
                                 @minprice  = @minprice,
                                 @maxprice  = @maxprice,
                                 @custname  = @custname,
                                 @city      = @city,
                                 @region    = @region,
                                 @country   = @country,
                                 @prodid    = @prodid,
                                 @prodname  = @prodname
   END
go
CREATE PROCEDURE search_orders_4a_sub1
                 @orderid   int,
                 @minprice  money,
                 @maxprice  money,
                 @prodid    int,
                 @prodname  nvarchar(40)  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 = @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
go
CREATE PROCEDURE search_orders_4a_sub2
                 @fromdate  datetime,
                 @todate    datetime,
                 @minprice  money,
                 @maxprice  money,
                 @custid    nchar(5),
                 @custname  nvarchar(40),
                 @city      nvarchar(15),
                 @region    nvarchar(15),
                 @country   nvarchar(15),
                 @prodid    int,
                 @prodname  nvarchar(40)  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.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  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
go
CREATE PROCEDURE search_orders_4a_sub3
                 @fromdate  datetime,
                 @todate    datetime,
                 @minprice  money,
                 @maxprice  money,
                 @custname  nvarchar(40),
                 @city      nvarchar(15),
                 @region    nvarchar(15),
                 @country   nvarchar(15),
                 @prodid    int,
                 @prodname  nvarchar(40) WITH RECOMPILE 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.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  (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