Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Zero to N Parameter Problem

By Sloan Holliday,

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.

Total article views: 11765 | Views in the last 30 days: 6
 
Related Articles
FORUM

Parameter selection causing screen refresh

Parameter selection causing screen refresh

BLOG

Report Parameter order in SSRS

Is the order of parameters important in SQL Server Reporting Services reports?  If you’ve got nested...

FORUM

SSIS Export to Flat File with a blank line b/w each Order

Anyone used MYOB for Orders/OrderDetails records? Need a line break between each OrderID?

FORUM

grouping based on parameter selected

grouping based on parameter selected

FORUM

Parameter Order-how change?

changing order of parametes to avoid "forward dependencies are not valid" error message

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones