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 UPDATE MyTable 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 @DelimitedList nvarchar(MAX) AS UPDATE MyTable SET Column1 = 1 WHERE ID IN (SELECT * FROM MyFunction) RETURN
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] GO INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Tom', 'Araya', 'email@example.com', 'False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Frank', 'Zappa', 'firstname.lastname@example.org','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Johann', 'Bach', 'email@example.com','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Mikael', 'Akerfeldt', 'firstname.lastname@example.org','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Sonny', 'Stitt', 'email@example.com','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)) RETURNS -- Create the schema of the Table that we will return to the caller @table TABLE (Number int) AS BEGIN -- 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) BEGIN -- 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)) ELSE 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) BEGIN -- Fill the table variable with the rows for your result set INSERT INTO @table (NUMBER) VALUES (@ItemInScope) END END RETURN END
- 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'. AS BEGIN -- Update User records where the UserId is in the @UserIdList UPDATE dbo.Users 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)) END GO
- 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:
EXEC [dbo].[SetReceiveNewsletter] @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 GO
Here are the results:
UserId FirstName LastName EmailAddress ReceiveNewsletter 1 Tom Araya firstname.lastname@example.org True 2 Frank Zappa email@example.com False 3 Johann Bach firstname.lastname@example.org False 4 Mikael Akerfeldt email@example.com True 5 Sonny Stitt firstname.lastname@example.org True
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.