Specifying 0 to N parameters for Stored Procedures with XML

While the topic has been discussed before, here is another look at the "Multiple but Optional Parameters" issue, when using TSQL. Sometimes you want to write a stored procedure which returns ResultSet(s), but you may or may not want to pass in some parameters. A common delimited list just does not seem to cut it anymore. And dynamic sql doesn't cut the mustard either.

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 TSQL to consider it. If I don't pass it in, I want TSQL to ignore it. And 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. It actually will get 3 ResultSets : 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 (~any combination of these filters you can think of). The "How to Use" shows how you can set 0 parameters, 1 parameter, or N number of parameters.

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 sysobjects
where id = object_id('uspOrderDetailsGetByXmlParams') and sysstat
& 0xf = 4)
BEGIN
 drop procedure dbo.uspOrderDetailsGetByXmlParams
END
GO

CREATE Procedure dbo.uspOrderDetailsGetByXmlParams(
@parametersXML Text
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
-- build a table (variable) to store the xml-based result set (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 set (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
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @parametersXML
--(Do not forget that XML (and xpaths below) are CASE SENSITIVE, no matter what your database collation happens to be.) --
INSERT INTO @orders
SELECT OrderID
FROM
 OPENXML (@hdoc, '/ParametersDS/Order', 2) WITH (
 OrderID int 'OrderID')
INSERT INTO @customers
SELECT CustomerID
FROM
 OPENXML (@hdoc, '/ParametersDS/Customer', 2) WITH (
 CustomerID varchar(5) 'CustomerID')
SELECT @dateOrderDateBefore = dateOfOrderOrigenBefore
FROM
 OPENXML (@hdoc, '/ParametersDS/SingleValueParam', 2) WITH (
 dateOfOrderOrigenBefore datetime 'OrderDateBefore')

SELECT @dateOrderDateAfter = dateOfOrderOrigenAfter
FROM
 OPENXML (@hdoc, '/ParametersDS/SingleValueParam', 2) WITH (
 dateOfOrderOrigenAfter datetime 'OrderDateAfter')
INSERT INTO @customerCountry
SELECT CountryName
FROM
 OPENXML (@hdoc, '/ParametersDS/CustomerCountry', 2) WITH (
 CountryName varchar(15) 'CountryName')
--Remove the handle to the XML document, since we're done with using the xmlDoc
EXEC sp_xml_removedocument @hdoc
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 join to the Customers table is only necessary because of the Country...
-- if you didn't want to use Customer.Country, you could leave this join out
INNER JOIN Customers c
 ON o.CustomerID = c.CustomerID
WHERE -- the parantheses play an important role, so be careful altering them
((@orderCount = 0) OR (o.OrderID IN ( Select OrderID from @orders ) ) )
AND
((@customerCount = 0) OR (o.CustomerID IN ( Select CustomerID from @customers ) ))
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
((@customerCountryCount = 0) OR (c.Country IN ( Select CountryName from @customerCountry ) ))
-- ORDER BY is unnecessary here

-------------------- Below are 3 queries/result sets we're interested in. Notice the piggyback 
-- off the @ordersWhichMetCriteriaTable every time.
--ResultSet #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 INNER JOIN
 Orders o ON c.CustomerID = o.CustomerID
WHERE
o.OrderID in ( Select OrderID from @ordersWhichMetCriteriaTable
)
ORDER BY
c.CustomerID

--ResultSet #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
o.OrderID in ( Select OrderID from @ordersWhichMetCriteriaTable )
ORDER BY
o.CustomerID , o.OrderID
--ResultSet #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
od.OrderID in ( Select OrderID from @ordersWhichMetCriteriaTable )
ORDER BY
od.OrderID
GO
--  The user stored procedure definition is above, the use of the user stored procedure is below.  
-- (Put the below code in a new Query Analyser window)
-- "How to Use" the procedure above.  (Put this code in a new Query Analyser window.)

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

 

 

 

 
print '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>

'

 

 

 

 

 

 
print 'Order ID that does not exist'

EXEC uspOrderDetailsGetByXmlParams '

<ParametersDS>
 <Order>

  <OrderID>-9999</OrderID>

 </Order>

 
</ParametersDS>

'

 
GO

--  END TSQL CODE 

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.