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

DECLARE @pString VARCHAR(2000)
SELECT @pString = 'Produce,Condiments,Meat/Poultry,Seafood'
EXEC usp_DelimitedString @pString