(Note: We are re-running this as we had a newsletter issue last week)
While this topic has been discussed before, here is another look at the "Multiple but Optional Parameters" issue in T-SQL. Sometimes you want to write a stored procedure which returns one or more Result(s), but you may or may not want to pass in some parameters. A comma delimited list just does not seem to cut it anymore, and dynamic SQL isn't a good idea.
All of us have been faced at times with a problems like these:
- What if you want to sometimes pass in a parameter?
- How do you handle multiple parameters?
I call this the "0:N Parameter" problem. If I pass in a parameter, I want the procedure to use it. If I don't pass it in, I want the procedure to ignore it. Of course, sometimes I want to have more flexibility than just 'no parameters or 1 parameter', or said another way, I want to specify N parameters, where N is 1 through infinity.
The below example uses the Northwind database to return Orders. The code will return three results : Customer, Order, and Order Detail information. The example allows you to specify filters such as: by OrderID , by Customer, an OrderDate before a certain date, an OrderDate after a certain date, and/or by Customer-Country. Essentially you can specify any combination of these filters you choose. The "How to Use" shows how you can set 0 parameters, 1 parameter, or N number of parameters.
How does it work?
- It takes input as XML and then shreds the XML
- The 0,1,N values are shredded into @variable tables
- The scalars are shredded into @scalar variables.
- A COUNT variable is established on the @variable tables (holding the 0,1,N values).
- Another @variable table ( @ordersWhichMetCriteriaTable) is created to hold the unique keys.
- The @ordersWhichMetCriteriaTable is populated, using the @variable tables, the @scalars, and the COUNT variables.
- 3 Select statements piggy back on the values in the @ordersWhichMetCriteriaTable table.
When parameters are of a "N" nature (zero, one, or more), the parameters are stored in a @holder (variable) table. Then a seperate @count variable is used, which is the indicator that you have or you don't have some of these parameters.
When parameters are of a scalar nature, they are stored in a @variable. This @variable can either be null (you didn't specify it) or populated (you did specify it).
By combining the @holder, the @count and the @scalar "tricks", you can get the filtered data as needed.
This method is not a "cure all" method. There is a performance penalty for using this method. If you have a procedure where performance is the main goal, and you have a lot of records to go through, this solution may not be the answer. I typically use this procedure for Report Generation, where the User may or may not specify input parameters.
If you're a DotNet developer, and like to create a strongly typed DataSets, you can create one and fill in values that way (And send in the .GetXml() of the DataSet as the parameter). You'll notice my syntax of "ParametersDS", that is not an accident.
Good luck. The method is useful ~at times. Please be aware of possible scalability issues, since there is a performance hit by making the procedure so flexible. I learned this method from a previous colleague. The example is an original one coded against the Northwind database.
/* START TSQL CODE *//* Stored Procedure Definition */Use Northwind
GO
IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsGetByXmlParams'
)
BEGIN
DROP PROCEDURE [dbo].[uspOrderDetailsGetByXmlParams]
END
GO
CREATE Procedure dbo.uspOrderDetailsGetByXmlParams(
@parametersXML XML
)
AS
BEGIN
SET NOCOUNT ON
/* build a table (variable) to store the xml-based result (for specific orderid's) */DECLARE @orderCount int
DECLARE @orders TABLE ( /* used to track which specific OrderID's you want */OrderID int
)
DECLARE @customerCount int DECLARE @customers TABLE ( /* used to track which specific customers you want */CustomerID varchar(5)
)
declare @dateOrderDateAfter datetime /* used to track with orders with OrderDate after you want */declare @dateOrderDateBefore datetime /* used to track with orders with OrderDate before you want *//* build a table (table-variable) to store the xml-based result (for specific Countries) */DECLARE @customerCountryCount int DECLARE @customerCountry TABLE ( /* used to track which specific Countries you want */CountryName varchar(15) )
/* Start XML usage */
/* Only incur the penalty of XML parsing, if XML was specified */if (@parametersXML IS NOT NULL) AND (Datalength(@parametersXML) > 10 )
/* Only process the xml If the xml exists, and it has at least 10 chars. 10 is just a somewhat */
/* arbritrary number, saying, that an xml doc with <10 chars doesn't have a whole lot going for it */ /* || DataLength is used for Text datatype */BEGIN
/* (Do not forget that XML (and xpaths below) are CASE SENSITIVE, no matter what your database collation happens to be.) */INSERT INTO @orders ( OrderID )
SELECT T.parameter.value('(OrderID)[1]', 'INT') AS OrderID
FROM @parametersXML.nodes('ParametersDS/Order') AS T(parameter);
INSERT INTO @customers (CustomerID)
SELECT T.parameter.value('(CustomerID)[1]', 'varchar(5)') AS CustomerID
FROM @parametersXML.nodes('ParametersDS/Customer') AS T(parameter);
/* Note, a single scalar value below */SELECT @dateOrderDateBefore = (
SELECT T.parameter.value('(OrderDateBefore)[1]', 'datetime') AS OrderDateBefore
FROM @parametersXML.nodes('ParametersDS/SingleValueParam') AS T(parameter) );
/* Note, a single scalar value below */SELECT @dateOrderDateAfter = (
SELECT T.parameter.value('(OrderDateAfter)[1]', 'datetime') AS OrderDateAfter
FROM @parametersXML.nodes('ParametersDS/SingleValueParam') AS T(parameter) );
INSERT INTO @customerCountry (CountryName)
SELECT T.parameter.value('(CountryName)[1]', 'varchar(15)') AS CountryName
FROM @parametersXML.nodes('ParametersDS/CustomerCountry') AS T(parameter);
END
/* End XML usage */
/* These count variables help distinquish between when a parameter is and isn't specified */select @orderCount = count(*) from @orders
select @customerCount = count(*) from @customers
select @customerCountryCount = count(*) from @customerCountry
/* Note, if the xml doesn't supply any dates, @dateOrderDateBefore and @dateOrderDateAfter will remain null *//* *//* Debugging queries *//*
select * from @orders
select * from @customers
print @dateOrderDateBefore
print @dateOrderDateAfter
select * from @customerCountry
*/
/* Above are the variables and variable-tables for parameters *//**/DECLARE @ordersWhichMetCriteriaTable TABLE ( /* used to track the orderid's we're interested in */OrderID int )
/* A new variable table holds (just) the OrderID's which meet the input parmeters.
You'll see the use of the @ordersWhichMetCriteriaTable later.
*/Insert into @ordersWhichMetCriteriaTable
SELECT
OrderID
FROM
Orders o
/* Note, this below join to the Customers table is only necessary because of the Country *//* if you didn't want to retrieve the Customer.Country column, you could leave this join out */INNER JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE
/* the parentheses play an important role, so be careful altering them *//* */(
(@orderCount = 0)
OR
( exists (select null from @orders innerVariableTable where innerVariableTable.OrderID = o.OrderID ))
)
/* */AND
(
(@customerCount = 0)
OR
( exists (select null from @customers innerVariableTable where innerVariableTable.CustomerID = o.CustomerID ))
)
/* */AND (( @dateOrderDateBefore IS NULL ) OR (o.OrderDate <= @dateOrderDateBefore ))
/* */AND (( @dateOrderDateAfter IS NULL ) OR (o.OrderDate >= @dateOrderDateAfter ))
/* */AND
/*CountryName is a string, so watch the case sensitivity, get around this by using UPPER() function */(
(@customerCountryCount = 0)
OR
( exists (select null from @customerCountry innerVariableTable where UPPER(innerVariableTable.CountryName) = UPPER(c.Country) ))
)
/* ORDER BY is unnecessary here */
/* Below are 3 queries/results we're interested in. Notice the piggyback off the @ordersWhichMetCriteriaTable every time. */
/* *//* *//* Result #1 *//* All Customer Information (for the specific orders in the @ordersWhichMetCriteriaTable table) */SELECT
c.CustomerID, c.CompanyName,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country ,c.Phone,c.Fax
FROM
Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE
exists (select null from @ordersWhichMetCriteriaTable innerHolder where innerHolder.OrderID = o.OrderID )
ORDER BY
c.CustomerID
/* *//* Result #2 */
/*All Order Information (for the specific orders in the @ordersWhichMetCriteriaTable table) */SELECT o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate,o.RequiredDate,o.ShippedDate,o.ShipVia ,o.Freight,o.ShipName,o.ShipAddress,o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate
FROM
Orders o
WHERE
exists (select null from @ordersWhichMetCriteriaTable innerHolder where innerHolder.OrderID = o.OrderID )
ORDER BY
o.CustomerID , o.OrderID
/* *//* Result #3 *//* All Order Detail Information (for the specific orders in the @ordersWhichMetCriteriaTable table) */ SELECT od.OrderID,od.ProductID,od.UnitPrice,od.Quantity,od.Discount
FROM
[Order Details] od
WHERE
exists (select null from @ordersWhichMetCriteriaTable innerHolder where innerHolder.OrderID = od.OrderID )
ORDER BY
od.OrderID
SET NOCOUNT OFF
END
GO
/* "How to Use" (the procedure above) */Use Northwind
GO
/* no parameters print 'No Filters, Just Give me back all the Data' */EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS> </ParametersDS> '
GO
/* just CustomerID print 'Filter on specific Customers' */EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS>
<Customer> <CustomerID>CENTC</CustomerID> </Customer> <Customer> <CustomerID>GROSR</CustomerID> </Customer>
</ParametersDS> '
GO
/* Order Dates (Before) */print 'Filter on the OrderDates being Before'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS> <SingleValueParam> <OrderDateBefore>7/7/1996</OrderDateBefore> </SingleValueParam> </ParametersDS> '
GO
/* Order Dates (After) */print 'Filter on the OrderDates being After'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS> <SingleValueParam> <OrderDateAfter>5/5/1998</OrderDateAfter> </SingleValueParam> </ParametersDS> '
GO
/* Order Dates (both) */print 'Filter on the OrderDates being (before and after) the input dates'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS> <SingleValueParam> <OrderDateBefore>12/31/1997</OrderDateBefore> <OrderDateAfter>1/1/1997</OrderDateAfter> </SingleValueParam> </ParametersDS> '
GO
/* 'Filter on specific OrderIDs' */EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS>
<Order> <OrderID>10265</OrderID> </Order> <Order> <OrderID>10267</OrderID> </Order> <Order> <OrderID>10269</OrderID> </Order>
</ParametersDS> '
GO
/* Specific Countries */print 'Filter on specific Countries'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS>
<CustomerCountry> <CountryName>Austria</CountryName> </CustomerCountry>
<CustomerCountry> <CountryName>Belgium</CountryName> </CustomerCountry>
</ParametersDS> '
GO
/* Specific Countries */print 'Filter on specific Countries and OrderDate'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS>
<CustomerCountry> <CountryName>Austria</CountryName> </CustomerCountry>
<CustomerCountry> <CountryName>Belgium</CountryName> </CustomerCountry>
<SingleValueParam> <OrderDateBefore>2/28/1997</OrderDateBefore> <OrderDateAfter>1/1/1997</OrderDateAfter> </SingleValueParam>
</ParametersDS> '
/* Empty Results */print 'Order ID that does not exist'
EXEC uspOrderDetailsGetByXmlParams ' <ParametersDS>
<Order> <OrderID>-9999</OrderID> </Order>
</ParametersDS> '
GO
Conclusions
This is my preferred method of handling 0:N parameters. As I said, this is a good approach for needs like Reporting, where you know up front the 0:N parameter need. Please heed my warning about scalability, and do not consider this a cure-all.
I hope you have learned a new method of passing parameters to a stored procedure that allows flexiblity on those passed in parameters.
