CREATE PROCEDURE search_orders_2                                   --  1
                 @orderid   int          = NULL,                   --  2
                 @fromdate  datetime     = NULL,                   --  3
                 @todate    datetime     = NULL,                   --  4
                 @minprice  money        = NULL,                   --  5
                 @maxprice  money        = NULL,                   --  6
                 @custid    nchar(5)     = NULL,                   --  7
                 @custname  nvarchar(40) = NULL,                   --  8
                 @city      nvarchar(15) = NULL,                   --  9
                 @region    nvarchar(15) = NULL,                   -- 10
                 @country   nvarchar(15) = NULL,                   -- 11
                 @prodid    int          = NULL,                   -- 12
                 @prodname  nvarchar(40) = NULL,                   -- 13
                 @debug     bit          = 0 AS                    -- 14
                                                                   -- 15
DECLARE @sql1        nvarchar(4000),                               -- 16
        @sql2        nvarchar(4000),                               -- 17
        @fromdatestr char(23),                                     -- 18
        @todatestr   char(23),                                     -- 19
        @minpricestr varchar(25),                                  -- 20
        @maxpricestr varchar(25)                                   -- 21
                                                                   -- 22
SELECT @fromdatestr = convert(char(23), @fromdate, 126),           -- 23
       @todatestr   = convert(char(23), @todate, 126),             -- 24
       @minpricestr = convert(varchar(25), @minprice),             -- 25
       @maxpricestr = convert(varchar(25), @maxprice)              -- 26
                                                                   -- 27
SELECT @sql1 =                                                     -- 28
   'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,      -- 29
           c.CustomerID, c.CompanyName, c.Address, c.City,         -- 30
           c.Region, c.PostalCode, c.Country, c.Phone,             -- 31
           p.ProductID, p.ProductName, p.UnitsInStock,             -- 32
           p.UnitsOnOrder                                          -- 33
    FROM   dbo.Orders o                                            -- 34
    JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID        -- 35
    JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID          -- 36
    JOIN   dbo.Products p ON p.ProductID = od.ProductID            -- 37
    WHERE  1 = 1'                                                  -- 38
                                                                   -- 39
IF @orderid IS NOT NULL                                            -- 40
   SELECT @sql1 = @sql1 + ' AND o.OrderID = ' + str(@orderid) +    -- 41
                          ' AND od.OrderID = ' + str(@orderid)     -- 42
                                                                   -- 43
IF @fromdate IS NOT NULL                                           -- 44
   SELECT @sql1 = @sql1 + ' AND o.OrderDate >= ' +                 -- 45
                          quotename(@fromdatestr, '''')            -- 46
                                                                   -- 47
IF @todate IS NOT NULL                                             -- 48
   SELECT @sql1 = @sql1 + ' AND o.OrderDate <= ' +                 -- 49
                          quotename(@todatestr, '''')              -- 50
                                                                   -- 51
IF @minprice IS NOT NULL                                           -- 52
   SELECT @sql1 = @sql1 + ' AND od.UnitPrice >= ' + @minpricestr   -- 53
                                                                   -- 54
IF @maxprice IS NOT NULL                                           -- 55
   SELECT @sql1 = @sql1 + ' AND od.UnitPrice <= ' + @maxpricestr   -- 56
                                                                   -- 57
SELECT @sql2 = ''                                                  -- 58
                                                                   -- 59
IF @custid IS NOT NULL                                             -- 60
   SELECT @sql2 = @sql2 + ' AND o.CustomerID = ' +                 -- 61
                          quotename(@custid, '''') +               -- 62
                          ' AND c.CustomerID = ' +                 -- 63
                          quotename(@custid, '''')                 -- 64
                                                                   -- 65
IF @custname IS NOT NULL                                           -- 66
   SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ' +             -- 67
                          quotename(@custname + '%', '''')         -- 68
                                                                   -- 69
IF @city IS NOT NULL                                               -- 70
   SELECT @sql2 = @sql2 + ' AND c.City = ' +                       -- 71
                          quotename(@city, '''')                   -- 72
                                                                   -- 73
IF @region IS NOT NULL                                             -- 74
   SELECT @sql2 = @sql2 + ' AND c.Region = ' +                     -- 75
                          quotename(@region, '''')                 -- 76
                                                                   -- 77
IF @country IS NOT NULL                                            -- 78
   SELECT @sql2 = @sql2 + ' AND c.Country = ' +                    -- 79
                          quotename(@country, '''')                -- 80
                                                                   -- 81
IF @prodid IS NOT NULL                                             -- 82
   SELECT @sql2 = @sql2 + ' AND od.ProductID = ' + str(@prodid) +  -- 83
                          ' AND p.ProductID = ' + str(@prodid)     -- 84
                                                                   -- 85
IF @prodname IS NOT NULL                                           -- 86
   SELECT @sql2 = @sql2 + ' AND p.ProductName LIKE ' +             -- 87
                          quotename(@prodname + '%', '''')         -- 88
                                                                   -- 89
IF @debug = 1                                                      -- 90
   PRINT @sql1 + @sql2                                             -- 91
                                                                   -- 92
EXEC(@sql1 + @sql2 + ' ORDER BY o.OrderID')                        -- 93