CREATE FUNCTION  search_orders_11fun (
                 @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) 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
   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)
)
go
CREATE PROCEDURE search_orders_11
                 @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 OrderID, OrderDate, UnitPrice, Quantity,
               CustomerID, CompanyName, Address, City, Region,
               PostalCode, Country, Phone, ProductID,
               ProductName, UnitsInStock, UnitsOnOrder
        FROM   dbo.search_orders_11fun (' +

        CASE WHEN @orderid IS NOT NULL
             THEN '@orderid'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @fromdate IS NOT NULL
             THEN '@fromdate'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @todate IS NOT NULL
             THEN '@todate'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @minprice IS NOT NULL
             THEN '@minprice'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @maxprice IS NOT NULL
             THEN '@maxprice'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @custid IS NOT NULL
             THEN '@custid'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @custname IS NOT NULL
             THEN '@custname'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @city IS NOT NULL
             THEN '@city'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @region IS NOT NULL
             THEN '@region'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @country IS NOT NULL
             THEN '@country'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @prodid IS NOT NULL
             THEN '@prodid'
             ELSE 'DEFAULT'
        END + ', ' +

        CASE WHEN @prodname IS NOT NULL
             THEN '@prodname'
             ELSE 'DEFAULT'
        END + ')' +

        'ORDER BY OrderID'

IF @debug = 1
   PRINT @sql

SELECT @paramlist = '@orderid   int,
                     @fromdate  datetime,
                     @todate    datetime,
                     @minprice  money,
                     @maxprice  money,
                     @custid    nchar(5),
                     @custname  nvarchar(40),
                     @city      nvarchar(15),
                     @region    nvarchar(15),
                     @country   nvarchar(15),
                     @prodid    int,
                     @prodname  nvarchar(40)'

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