This article uses XML to deal with optional parameters along with multiple parameter passed into a stored procedure. (Note: We are re-running this as we had a newsletter issue last week)
In this article I would like to go over the options that DBAs or developers can use when a table or the multiple rows of values need to be sent to a stored procedure as a parameter. With today’s technology we have at least three different options to accomplish this task. Let’s list all the possibilities and then go over the details:
- Use delimited string
- Use XML data type
- Use User-Defined Table type
Calling a stored procedure to send each individual row in a cursor-based process is not considered a table and is not discussed in this article.
The Delimited String Option
Using a delimited string is a good option when a parameter’s values will be used with the WHERE clause with an IN predicate as a multi-value filter for a query. A delimited string is easy to build either in .NET or SQL Server.
For example, suppose we received a task to create a stored procedure to return a result set when the WHERE clause could have more than one value. We want then to pass a number of such values as one parameter (with values separated by a delimiter character) to a Stored Procedure or User-Defined Function (UDF). A traditional way to parse such delimited sting would be in a loop, cutting the string with T-SQL String functions, as in this example:
-- This would be the input parameter of a stored procedure, if you want to do it that way, or a UDF: declare @string varchar(500) set @string = ‘ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z’ -- Parsing the comma-delimited string: declare @pos int declare @piece varchar(500) – Need to tack a delimiter onto the end of the input string if one doesn’t exist if right(rtrim(@string),1) <> ‘,’ set @string = @string + ‘,’ set @pos = patindex(‘%,%’ , @string) while @pos <> 0 begin set @piece = left(@string, @pos – 1) – You have a piece of data, so insert it, print it, do whatever you want to with it. print cast(@piece as varchar(500)) set @string = stuff(@string, 1, @pos, ”) set @pos = patindex(‘%,%’ , @string) end
In other RDBMS/other flavors of SQL languages (or most other programming languages, for that matter) this code would look similar.
Starting from MS SQL Server 2005 and onwards, T-SQL provides a more efficient (yet more complicated) way of parsing a delimited string, using a number of new features available in T-SQL of the latest versions of SQL Server such as CTE. To illustrate parsing a delimited string, let us use the Northwind sample database. We will do this within a stored procedure using a CTE against the Orders and Categories tables. The table Categories contains the names of categories of products, and both tables are joined by a common field CategoryID. The column CategoryName will be used as a filter.
The task is simple in this demo: to display a list of products that belong to a variable number of categories. A sample result set is shown in Figure 1.1.
One time we call the procedure below, named usp_DelimitedString, and we may want to see all products belonging to the Condiments category, but another time we need a report on products associated with Contiments, Produce, and Meat/Paultry. The next time we may want to pass twenty different categories, separating one value from another by comma as a delimeter.
How do we pass this variable number of values to a stored procedure as parameter(s)? A delimited string has been a traditional option for quite some time. A string consisting of a varied number of categories separated by comma or any other delimiter of user choice. This is how you can process a delimited string via T-SQL using CTE feature.
First, create the stored procedure:
USE Northwind; GO CREATE PROC usp_DelimitedString @CategoryList nvarchar(500) AS declare @pDelimiter char(1) SET @pDelimiter = ',' ;WITH NUM1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), NUM2(N) AS (SELECT 1 FROM NUM1 a, NUM1 b), NUM4(N) AS (SELECT 1 FROM NUM2 a, NUM2 b), CTE_Parse(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM NUM4), CTE_Filter AS ( SELECT SUBSTRING(@CategoryList, N, CHARINDEX(@pDelimiter, @CategoryList + @pDelimiter, N) - N) AS Item FROM CTE_Parse WHERE N <= LEN(@CategoryList) AND SUBSTRING(@pDelimiter + @CategoryList, N, 1) = @pDelimiter ) SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice FROM dbo.Categories JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID JOIN CTE_Filter ON Categories.CategoryName = CTE_Filter.Item GO
Next, call the stored procedure, usp_Delimitedstring
DECLARE @pString VARCHAR(2000) SELECT @pString = 'Produce,Condiments,Meat/Poultry,Seafood' EXEC usp_DelimitedString @pString
In the example above, using a delimited string is an acceptable and well-known working option for processing a “one column table” or a single dimensional array as a parameter. Developers are familiar with this technique, which is similar in a number of programming languages. It is however not possible to represent a multi-dimensional array (or multi-row table) in one character string parameter. Another disadvantage with delimited strings is that they require a loop-based parsing process, which can become not only cumbersome but also resource consuming, depending on a size of the string being passed as an input parameter and parsed. The CTE is new and not clear to many developers yet.
Let’s review another option.