From time to time there is a need to convert a delimited list into a SQL table for comparison. The classic example is passing a parameter which contains a set of values to SQL. For example get me all products in categories 1,2,5,6,8. With Dynamic SQL this is easy as you can simply use a nested in TSQL statement
For Example:
Select *
From product Table
Where categoryid in (1,2,5,6,8)
Now what happens if you are required or want to you use stored procedures. In this case the following will not work
Create procedure ExampleProc @categorylist varchar (max) as
BEGIN
Select *
From productTable
Where categoryid in @categorylist
END
Exec ExampleProc ‘1,2,5,6,8’ -- will not work.
To solve this we can make SQL server convert a list into a table using the function below:
Create Function fn_ParseIntArray (@Array varchar(8000), @separator char(1))
RETURNS @ParseIntArray table (i int)
AS
BEGIN
-- Created by Kevin Bosch
-- Date 16-Nov-2002
-- Part Of the Code Associate Codebase for SQL
-- ©2003 Code Associate
-- do not copy without permission
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
-- For the loop to work an extra separator at the end is needed. The Code will always
-- look to the left of the separator character for each array value
set @array = @array + @separator
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
-- This is where the code inserts the left most value in the array into the table
-- the value will be cast as an integer when going into the table if it is not a valid
-- integer it will raise an exception
if len(@array) > 1
insert into @ParseIntArray values (left(@array, @separator_position - 1))
-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
RETURN
END
Example of usage.
Create procedure ExampleProc2 @categorylist
varchar (
max)
AS
BEGIN
Select *
From productTable
Where categoryid
in dbo.fn_ParseIntArray(@categorylist, ‘,’)
END
Exec ExampleProc2 ‘1,2,5,6,8’ -- will work.
Now SQL server will take the ‘1,2,5,6,8’ and convert it into a table array of integers which it can then use in a nested statement. Whilst this is useful and quick it is not the most optimal method for doing this. A .NET function written and deployed in SQL will outperform this method, or the use of dynamic SQL should be considered.