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

   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 OR @orderid IS NULL)
     AND  (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)