Sending a Variable Number of Elements in a Parameter

Alex Grinberg, 2014-02-06

Introduction

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:

  1. Use delimited string
  2. Use XML data type
  3. 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.

Figure 1.1.

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.

The XML data type option

Rate

3.47 (34)

Share

Share

Rate

3.47 (34)

Related content

Saving a Sales Order Part 1

How many times have you tried to save a sales order to your database? For many DBAs this is a common scenario and one of the challenges is the many round trips for the various line items. Jacob Sebastian brings us the first part of a four part series looking at how you can use XML to reduce the round trips in SQL Server 2000.

4.67 (3)

Jacob Sebastian

2007-03-27

12,091 reads