CREATE VIEW search_order_view 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
go

   CREATE FUNCTION search_order_fun () RETURNS TABLE AS
      RETURN(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)
   go
   CREATE PROCEDURE search_orders_8
                    @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(4000),
           @paramlist  nvarchar(4000)

   SELECT @sql = 'SELECT * FROM dbo.search_order_view WHERE 1 = 1 '

   IF @orderid IS NOT NULL
      SELECT @sql = @sql + ' AND OrderID = @xorderid'

   IF @fromdate IS NOT NULL
      SELECT @sql = @sql + ' AND OrderDate >= @xfromdate'

   IF @todate IS NOT NULL
      SELECT @sql = @sql + ' AND OrderDate <= @xtodate'

   IF @minprice IS NOT NULL
      SELECT @sql = @sql + ' AND UnitPrice >= @xminprice'

   IF @maxprice IS NOT NULL
      SELECT @sql = @sql + ' AND UnitPrice <= @xmaxprice'

   IF @custid IS NOT NULL
      SELECT @sql = @sql + ' AND CustomerID = @xcustid'

   IF @custname IS NOT NULL
      SELECT @sql = @sql + ' AND CompanyName LIKE @xcustname + ''%'''

   IF @city IS NOT NULL
      SELECT @sql = @sql + ' AND City = @xcity'

   IF @region IS NOT NULL
      SELECT @sql = @sql + ' AND Region = @xregion'

   IF @country IS NOT NULL
      SELECT @sql = @sql + ' AND Country = @xcountry'

   IF @prodid IS NOT NULL
      SELECT @sql = @sql + ' AND ProductID = @xprodid'

   IF @prodname IS NOT NULL
      SELECT @sql = @sql + ' AND ProductName LIKE @xprodname + ''%'''

   SELECT @sql = @sql + ' ORDER BY OrderID'

   IF @debug = 1
      PRINT @sql

   SELECT @paramlist = '@xorderid   int,
                        @xfromdate  datetime,
                        @xtodate    datetime,
                        @xminprice  money,
                        @xmaxprice  money,
                        @xcustid    nchar(5),
                        @xcustname  nvarchar(40),
                        @xcity      nvarchar(15),
                        @xregion    nvarchar(15),
                        @xcountry   nvarchar(15),
                        @xprodid    int,
                        @xprodname  nvarchar(40)'

   EXEC sp_executesql @sql, @paramlist,
                      @orderid, @fromdate, @todate, @minprice, @maxprice,
                      @custid, @custname, @city, @region, @country,
                      @prodid, @prodname