CREATE PROCEDURE search_orders_10  @orderid   int      = NULL,
                                   @custid    nchar(5) = NULL,
                                   @fromdate  datetime = NULL,
                                   @todate    datetime = NULL,
                                   @empid     smallint = NULL,
                                   @shipdate  datetime = NULL AS

IF @fromdate IS NOT NULL AND @todate IS NULL
   SELECT @todate = MAX(OrderDate) FROM Orders
ELSE IF @fromdate IS NULL AND @todate IS NULL
   SELECT @fromdate = MIN(OrderDate) FROM Orders

SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
       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   (SELECT TOP 99.9999999 PERCENT
               o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
               o.CustomerID
        FROM   Orders o
        WHERE  (o.OrderID     = @orderid   AND @orderid   IS NOT NULL)
           OR  (o.CustomerID  = @custid    AND @custid    IS NOT NULL)
           OR  (o.EmployeeID  = @empid     AND @empid     IS NOT NULL)
           OR  (o.ShippedDate = @shipdate  AND @shipdate  IS NOT NULL)
           OR  (o.OrderDate   BETWEEN @fromdate AND @todate AND
                @fromdate IS NOT NULL AND @todate IS NOT NULL)
        ) AS 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   OR @orderid   IS NULL)
  AND  (o.CustomerID = @custid    OR @custid    IS NULL)
  AND  (o.EmployeeID = @empid     OR @empid     IS NULL)
  AND  (o.OrderDate  >= @fromdate OR @fromdate  IS NULL)
  AND  (o.OrderDate  <= @todate   OR @todate    IS NULL)
ORDER BY o.OrderID, od.ProductID
go
CREATE PROCEDURE search_orders_10a @orderid    int      = NULL,
                                   @custid     nchar(5) = NULL,
                                   @fromdate   datetime = NULL,
                                   @todate     datetime = NULL,
                                   @empid      smallint = NULL,
                                   @shipdate   datetime = NULL,
                                   @prodid     int      = NULL AS

IF @fromdate IS NOT NULL AND @todate IS NULL
   SELECT @todate = MAX(OrderDate) FROM Orders
ELSE IF @fromdate IS NULL AND @todate IS NULL
   SELECT @fromdate = MIN(OrderDate) FROM Orders

SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
       o.UnitPrice, o.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   (SELECT TOP 99.9999999 PERCENT
               o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
               o.CustomerID, od.UnitPrice, od.Quantity, od.ProductID
        FROM   Orders o
        JOIN   [Order Details] od ON od.OrderID = o.OrderID
        WHERE  (o.OrderID     = @orderid   AND @orderid   IS NOT NULL)
           OR  (o.CustomerID  = @custid    AND @custid    IS NOT NULL)
           OR  (o.EmployeeID  = @empid     AND @empid     IS NOT NULL)
           OR  (o.ShippedDate = @shipdate  AND @shipdate  IS NOT NULL)
           OR  (o.OrderDate   BETWEEN @fromdate AND @todate AND
                @fromdate IS NOT NULL AND @todate IS NOT NULL)
           OR  (od.ProductID  = @prodid    AND @prodid    IS NOT NULL)
        ) AS o
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = o.ProductID
WHERE  (o.OrderID    = @orderid   OR @orderid   IS NULL)
  AND  (o.CustomerID = @custid    OR @custid    IS NULL)
  AND  (o.EmployeeID = @empid     OR @empid     IS NULL)
  AND  (o.OrderDate  >= @fromdate OR @fromdate  IS NULL)
  AND  (o.OrderDate  <= @todate   OR @todate    IS NULL)
  AND  (o.ProductID  = @prodid    OR @prodid    IS NULL)
ORDER BY o.OrderID, o.ProductID
go
CREATE PROCEDURE search_orders_10b @orderid   int          = NULL,
                                   @custid    nchar(5)     = NULL,
                                   @fromdate  datetime     = NULL,
                                   @todate    datetime     = NULL,
                                   @empid     smallint     = NULL,
                                   @shipdate  datetime     = NULL,
                                   @custname  nvarchar(80) = NULL AS

IF @fromdate IS NOT NULL AND @todate IS NULL
   SELECT @todate = MAX(OrderDate) FROM Orders
ELSE IF @fromdate IS NULL AND @todate IS NULL
   SELECT @fromdate = MIN(OrderDate) FROM Orders

SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
       od.UnitPrice, od.Quantity,
       o.CustomerID, o.CompanyName, o.Address, o.City, o.Region,
       o.PostalCode, o.Country, o.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM   (SELECT TOP 99.9999999 PERCENT
               o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
               o.CustomerID, c.CompanyName, c.Address, c.City,
               c.Region, c.PostalCode, c.Country, c.Phone
        FROM   Orders o
        JOIN   Customers c ON o.CustomerID = c.CustomerID
        WHERE  (o.OrderID     = @orderid   AND @orderid   IS NOT NULL)
           OR  (o.CustomerID  = @custid    AND @custid    IS NOT NULL)
           OR  (o.EmployeeID  = @empid     AND @empid     IS NOT NULL)
           OR  (o.ShippedDate = @shipdate  AND @shipdate  IS NOT NULL)
           OR  (o.OrderDate   BETWEEN @fromdate AND @todate AND
                @fromdate IS NOT NULL AND @todate IS NOT NULL)
           OR  (c.CompanyName LIKE @custname + '%' AND @custname  IS NOT NULL)
        ) AS o
JOIN   [Order Details] od ON od.OrderID = o.OrderID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID    = @orderid    OR @orderid    IS NULL)
  AND  (o.CustomerID = @custid     OR @custid     IS NULL)
  AND  (o.EmployeeID = @empid      OR @empid      IS NULL)
  AND  (o.OrderDate  >= @fromdate OR @fromdate  IS NULL)
  AND  (o.OrderDate  <= @todate   OR @todate    IS NULL)
  AND  (o.CompanyName  LIKE @custname + '%' OR @custname  IS  NULL)
ORDER BY o.OrderID, od.ProductID