This post is related to and a supplement to a previous post “Pass Delimited List to SQL Server Stored Procedure”
Since parsing a delimited string and using the values of a delimited items is a common task in T-SQL, it is a good idea to create a T-SQL Function that will do the work of parsing the delimited string and then return the values in a Table.
Once the Function is created, you can call the Function from a Stored Procedure, Query, or whatever else can call a Function.
For our example, imagine you have a Table of Users. Where the table consists of the Columns: FirstName, LastName, EmailAddress, and ReceiveNewsletter. Our imaginary application has a feature that will send out monthly newsletters to users that would like to receive the monthly newsletters. To keep track of whether or not a user would like to receive the newsletter, we have a column in the Users table that accepts a bit value (True/False).
Now imagine the scenario where you want to create a Store Procedure that will update the ReceiveNewsletter column for multiple users using only one UPDATE statement. To do this we will create a sproc that receives a delimited list of UserId’s and a True/False value that specifies whether or not the specified UserId’s want to receive the newsletter or not.
However, due to T-SQL limitations, we cannot simply create an UPDATE statement in our sproc that looks like:
CREATE PROCEDURE MySproc @ids varchar(50) AS
SET Col1 = 1
WHERE ID IN (@ids)
The result of calling the sproc above is an error message that reads something like:
Syntax error converting the varchar value ‘1,2,3’ to a column
of data type int.
(Assuming that the caller of the sproc sent “1,2,3” as the @ids value.
To work around this limitation, we will create a Function that does the work of parsing the delimited list of ids and then return the values in a Table. The caller of the Function can then simply SELECT the values from the returned table.
-- The caller of the sproc will send a delimited list of MyTable ID's such as "1,23,456,789"
-- Stored Procedure
CREATE PROCEDURE MySproc
SET Column1 = 1
WHERE ID IN (SELECT * FROM MyFunction)
Using Microsoft SQL Server Management Studio, this post will show you how to create the Function that will return a Table that consists of one column (named “Numbers”). The values/records of the “Numbers” column will consist of integer values that were parsed from a delimited string.
- Open Microsoft SQL Server Management Studio
- Open the database that you would like to add the Function to
- Add a table to the database named Users. You can add the table manually or just run this script on your database:
CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[EmailAddress] [nvarchar](50) NULL,
[ReceiveNewsletter] [bit] NULL
) ON [PRIMARY]
INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Tom', 'Araya', 'firstname.lastname@example.org', 'False')
INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Frank', 'Zappa', 'email@example.com','False')
INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Johann', 'Bach', 'firstname.lastname@example.org','False')
INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Mikael', 'Akerfeldt', 'email@example.com','False')
INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Sonny', 'Stitt', 'firstname.lastname@example.org','False')
- Navigate to the “Programmability” folder of your database
- Right-click the “Functions” folder and select “New < Multi-Statement Table-Valued Function”
- Add the following T-SQL code to your new Function:
-- The name of our Function will be "ParseNumbersFromDelimitedList" and will take two Parameters: 1. the delimited list and 2. the delimiter
CREATE FUNCTION [dbo].[ParseNumbersFromDelimitedList](@DelimitedList nvarchar(max),@Delimiter varchar(1))
-- Create the schema of the Table that we will return to the caller
-- Remove white space from the list
SET @DelimitedList = REPLACE(@DelimitedList, ' ','');
-- Var that will hold the value of the delimited item during the while-loop
DECLARE @ItemInScope VARCHAR(MAX)
WHILE(LEN(@DelimitedList) > 0)
-- If a delimiter exists in the string/list, then get the value to the left of the delimter.
-- Otherwise, just get the value of the string/list. This means that there is only one item in the string/list
IF(CHARINDEX(@Delimiter, @DelimitedList) > 0)
SET @ItemInScope = LEFT(@DelimitedList, CHARINDEX(@Delimiter, @DelimitedList))
SET @ItemInScope = @DelimitedList
-- Remove the @NumberInScope value from the @NumericList
SET @DelimitedList = RIGHT(@DelimitedList, LEN(@DelimitedList) - LEN(@ItemInScope))
-- Remove the delimiter from the @NumberInScope
SET @ItemInScope = REPLACE(@ItemInScope,@Delimiter,'')
-- Print only the integer values
IF(ISNUMERIC(@ItemInScope) = 1)
-- Fill the table variable with the rows for your result set
INSERT INTO @table
- Execute/Save the Function
- Now, we will create a Stored Procedure that will update the Users table. Specifically, we will create a sproc that will SET the “ReceiveNewsletter” column to ‘TRUE’ or ‘FALSE’ for records that have a matching UserId value that exists in a delimited string.
In SSMS, add the following sproc:
CREATE PROCEDURE SetReceiveNewsletter
@UserIdList NVARCHAR(MAX), -- The caller will pass a delimited string of UserId's
@Delimiter NVARCHAR(1) = ',', -- The caller can specify what the delimiter is. The default delimiter is a comma.
@ReceiveNewsletter bit = 'FALSE' -- The caller can specify 'TRUE' or 'FALSE' as the value that will update the records. Default is 'FALSE'.
-- Update User records where the UserId is in the @UserIdList
SET ReceiveNewsletter = @ReceiveNewsletter
WHERE UserId IN
-- Here is where we call the Function. The Function returns a Table of Numbers that existed in the @UserIdList
(SELECT * FROM dbo.ParseNumbersFromDelimitedList(@UserIdList,@Delimiter))
- We now have a Function that does the dirty work of parsing a delimited string and returning any integers that were in the string (the UserId’s) in a Table that has a column named “Numbers”. All we need to do now is call the Stored Procedure …
- Using SSMS, call the sproc:
@UserIdList = N'1,4,5', -- Here's a delimited string of UserId's that the caller passes to the sproc
@Delimiter = N',', -- The caller can specify the delimiter character
@ReceiveNewsletter = TRUE -- Set the ReceiveNewsletter column to TRUE for all UserId's that exist in the @UserIdList
Here are the results:
Since T-SQL doesn’t parse a delimited string when using the IN clause in a T-SQL statement, we need to first parse the values out of the delimited string variable then add those values to a T-SQL Table. Then, we can use that Table to select the values within our statement.
And since this is a common problem when SELECT’ing, UPDATE’ing, etc, we built a function that does the work of parsing the delimited string and adding those values to a Table. Callers of the Function simply have to pass the delimited string to the Function then work off of the returned Table.
If you are interested in learning more about working with lists and arrays in SQL Server, do yourself a favor and check out what Erland Sommarskog has to say.