CREATE PROCEDURE search_orders_2
                 @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,
                 @debug     bit          = 0 AS

DECLARE @sql         nvarchar(MAX),
        @fromdatestr char(23),
        @todatestr   char(23),
        @minpricestr varchar(25),
        @maxpricestr varchar(25)

SELECT @fromdatestr = convert(char(23), @fromdate, 126),
       @todatestr   = convert(char(23), @todate, 126),
       @minpricestr = convert(varchar(25), @minprice),
       @maxpricestr = convert(varchar(25), @maxprice)

SELECT @sql =
   '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   dbo.Orders o
    JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
    JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
    JOIN   dbo.Products p ON p.ProductID = od.ProductID
    WHERE  1 = 1'

IF @orderid IS NOT NULL
   SELECT @sql = @sql + ' AND o.OrderID = ' + str(@orderid) +
                        ' AND od.OrderID = ' + str(@orderid)

IF @fromdate IS NOT NULL
   SELECT @sql = @sql + ' AND o.OrderDate >= ' +
                        quotename(@fromdatestr, '''')

IF @todate IS NOT NULL
   SELECT @sql = @sql + ' AND o.OrderDate <= ' +
                        quotename(@todatestr, '''')

IF @minprice IS NOT NULL
   SELECT @sql = @sql + ' AND od.UnitPrice >= ' + @minpricestr

IF @maxprice IS NOT NULL
   SELECT @sql = @sql + ' AND od.UnitPrice <= ' + @maxpricestr

IF @custid IS NOT NULL
   SELECT @sql = @sql + ' AND o.CustomerID = ' +
                        quotename(@custid, '''') +
                        ' AND c.CustomerID = ' +
                        quotename(@custid, '''')

IF @custname IS NOT NULL
   SELECT @sql = @sql + ' AND c.CompanyName LIKE ' +
                        quotename(@custname + '%', '''')

IF @city IS NOT NULL
   SELECT @sql = @sql + ' AND c.City = ' +
                        quotename(@city, '''')

IF @region IS NOT NULL
   SELECT @sql = @sql + ' AND c.Region = ' +
                        quotename(@region, '''')

IF @country IS NOT NULL
   SELECT @sql = @sql + ' AND c.Country = ' +
                        quotename(@country, '''')

IF @prodid IS NOT NULL
   SELECT @sql = @sql + ' AND od.ProductID = ' + str(@prodid) +
                        ' AND p.ProductID = ' + str(@prodid)

IF @prodname IS NOT NULL
   SELECT @sql = @sql + ' AND p.ProductName LIKE ' +
                        quotename(@prodname + '%', '''')

IF @debug = 1
   PRINT @sql

EXEC(@sql + ' ORDER BY o.OrderID')