Many times, as developers, we come across situations where we need to pass an array of IDs as a parameter to a stored procedure in SQL Server. However, we often wish that we could pass this data as a table instead of a string. In this blog post, we will explore a solution to this problem using a recursive Common Table Expression (CTE).
The Problem
Let’s say we have a string of IDs: ‘1,2,4,8’ and we want to convert it into a table in SQL Server. The desired output would be:
| Item |
|---|
| 1 |
| 2 |
| 4 |
| 8 |
The Solution
To achieve this, we can create a user-defined function in SQL Server that takes the string and delimiter as input parameters and returns a table. Here is the function:
CREATE FUNCTION [dbo].[strToTable]
(
@array varchar(max),
@del char(1)
)
RETURNS
@listTable TABLE
(
item int
)
AS
BEGIN
WITH rep (item,list) AS
(
SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item,
SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list
UNION ALL
SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item,
SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list
FROM rep
WHERE LEN(rep.list) > 0
)
INSERT INTO @listTable
SELECT item FROM rep
RETURN
END
To use this function, you can call it as follows:
DECLARE @array VARCHAR(max)
SET @array = '1,2,4,8'
SELECT item FROM strToTable(@array,',')
How It Works
The function uses a recursive CTE (Common Table Expression) to convert the string to a table. A recursive CTE is a query that calls itself and reuses the result set until it is empty.
The function starts by splitting the string into two parts: the part before the delimiter and the part after the delimiter. It then inserts the first part into the table and recursively calls itself with the remaining part of the string until there are no more parts left.
By using this recursive approach, we can easily convert a string into a table in SQL Server.
Conclusion
In this blog post, we have explored a solution to the problem of converting a string to a table in SQL Server. By using a recursive CTE, we can easily split the string and insert its parts into a table. This technique can be useful in various scenarios where we need to work with arrays of IDs or other delimited values in SQL Server.