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 (Sql Server 2005 and Up -- Update)

By Sloan Holliday, (first published: 2013/12/10)

(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?

  1. It takes input as XML and then shreds the XML
  2. The 0,1,N values are shredded into @variable tables
  3. The scalars are shredded into @scalar variables.
  4. A COUNT variable is established on the @variable tables (holding the 0,1,N values).
  5. Another @variable table ( @ordersWhichMetCriteriaTable) is created to hold the unique keys.
  6. The @ordersWhichMetCriteriaTable is populated, using the @variable tables, the @scalars, and the COUNT variables.
  7. 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.

Total article views: 7143 | Views in the last 30 days: 3
 
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

ARTICLE

The Zero to N Parameter Problem

T-SQL has some well known limitations when working with parameters for a stored procedure, not the l...

Tags
stored procedures    
t-sql    
xml    
 
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