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

   DECLARE @mincustid nchar(10),
           @maxcustid nchar(10),
           @minint    int,
           @maxint    int

   SELECT @minint = convert(int, 0x80000000),
          @maxint = convert(int, 0x7FFFFFFF)

   IF @custid IS NULL
      SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
      FROM Customers

   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 BETWEEN coalesce(@orderid, @minint) AND
                            coalesce(@orderid, @maxint)
     AND  od.OrderID BETWEEN coalesce(@orderid, @minint) AND
                             coalesce(@orderid, @maxint)
     AND  o.OrderDate >= coalesce(@fromdate, '17530101')
     AND  o.OrderDate <= coalesce(@todate, '99991231')
     AND  od.UnitPrice >= coalesce(@minprice, 0)
     AND  od.UnitPrice <= coalesce(@maxprice, 1E18)
     AND  o.CustomerID BETWEEN coalesce(@custid, @mincustid) AND
                               coalesce(@custid, @maxcustid)
     AND  c.CustomerID BETWEEN coalesce(@custid, @mincustid) AND
                               coalesce(@custid, @maxcustid)
     AND  c.CompanyName LIKE coalesce(@custname + '%', '%')
     AND  c.City LIKE coalesce(@city, '%')
     AND  (c.Region = @region OR @region IS NULL)
     AND  (c.Country = @country OR @country IS NULL)
     AND  od.ProductID BETWEEN coalesce(@prodid, @minint) AND
                               coalesce(@prodid, @maxint)
     AND  p.ProductID  BETWEEN coalesce(@prodid, @minint) AND
                               coalesce(@prodid, @maxint)
     AND  p.ProductName LIKE coalesce(@prodname + '%', '%')
   ORDER  BY o.OrderID