CREATE PROCEDURE search_orders_1                                   --  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 @sql        nvarchar(4000),                                -- 16
        @paramlist  nvarchar(4000)                                 -- 17
                                                                   -- 18
SELECT @sql =                                                      -- 19
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 20
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 21
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 22
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 23
            p.UnitsOnOrder                                         -- 24
     FROM   dbo.Orders o                                           -- 25
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28
     WHERE  1 = 1'                                                 -- 29
                                                                   -- 30
IF @orderid IS NOT NULL                                            -- 31
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +             -- 32
                        ' AND od.OrderID = @xorderid'              -- 33
                                                                   -- 34
IF @fromdate IS NOT NULL                                           -- 35
   SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'           -- 36
                                                                   -- 37
IF @todate IS NOT NULL                                             -- 38
   SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'             -- 39
                                                                   -- 40
IF @minprice IS NOT NULL                                           -- 41
   SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'          -- 42
                                                                   -- 43
IF @maxprice IS NOT NULL                                           -- 44
   SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'          -- 45
                                                                   -- 46
IF @custid IS NOT NULL                                             -- 47
   SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +           -- 48
                        ' AND c.CustomerID = @xcustid'             -- 49
                                                                   -- 50
IF @custname IS NOT NULL                                           -- 51
   SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' -- 52
                                                                   -- 53
IF @city IS NOT NULL                                               -- 54
   SELECT @sql = @sql + ' AND c.City = @xcity'                     -- 55
                                                                   -- 56
IF @region IS NOT NULL                                             -- 57
   SELECT @sql = @sql + ' AND c.Region = @xregion'                 -- 58
                                                                   -- 59
IF @country IS NOT NULL                                            -- 60
   SELECT @sql = @sql + ' AND c.Country = @xcountry'               -- 61
                                                                   -- 62
IF @prodid IS NOT NULL                                             -- 63
   SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +           -- 64
                        ' AND p.ProductID = @xprodid'              -- 65
                                                                   -- 66
IF @prodname IS NOT NULL                                           -- 67
   SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' -- 68
                                                                   -- 69
SELECT @sql = @sql + ' ORDER BY o.OrderID'                         -- 70
                                                                   -- 71
IF @debug = 1                                                      -- 72
   PRINT @sql                                                      -- 73
                                                                   -- 74
SELECT @paramlist = '@xorderid   int,                              -- 75
                     @xfromdate  datetime,                         -- 76
                     @xtodate    datetime,                         -- 77
                     @xminprice  money,                            -- 78
                     @xmaxprice  money,                            -- 79
                     @xcustid    nchar(5),                         -- 80
                     @xcustname  nvarchar(40),                     -- 81
                     @xcity      nvarchar(15),                     -- 82
                     @xregion    nvarchar(15),                     -- 83
                     @xcountry   nvarchar(15),                     -- 84
                     @xprodid    int,                              -- 85
                     @xprodname  nvarchar(40)'                     -- 86
                                                                   -- 87
EXEC sp_executesql @sql, @paramlist,                               -- 88
                   @orderid, @fromdate, @todate, @minprice,        -- 89
                   @maxprice,  @custid, @custname, @city, @region, -- 90
                   @country, @prodid, @prodname                    -- 91