Sending multiple values in the form of array to a SQL Server stored procedure is very common among database developers. We can send the values in either of the two methods as following:
· Comma separated values (in a single string)
· XML document (in a single string)
This post focuses on comma separated values.
The following converts a parameter containing a string (array) of values (separated by commas) to a table:
CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS
begin
DECLARE @TempList table
(
Value varchar(30)
)
DECLARE @Value varchar(30), @Pos int
SET @Parameters = LTRIM(RTRIM(@Parameters))+ ‘,’
SET @Pos = CHARINDEX(‘,’, @Parameters, 1)
IF REPLACE(@Parameters, ‘,’, ”) <> ”
BEGIN
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos – 1)))
IF @Value <> ”
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value)
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) – @Pos)
SET @Pos = CHARINDEX(‘,’, @Parameters, 1)
END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
The above function can be tested with the following statement:
select * from dbo.ParmsToList (‘1001,2323,jagadish,pulakhandam,3232’)
We can utilize the above function and scale out to more functions according to our requirements. For example, I would like to have an “IN” parameter list to be returned for a given string of comma separated values. I can implement a new function (which in-turn invokes the above function) as following:
create function dbo.ParmsToINValues (@Parameters varchar(500))
returns varchar(1000)
AS
begin
DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL, –THE IDENTITY STATEMENT IS IMPORTANT!
item varchar(200)
)
insert into @item_table select * from dbo.ParmsToList (@Parameters)
DECLARE @item varchar(200)
DECLARE @item_counter INT
DECLARE @loop_counter INT
DECLARE @result varchar(1000)
SELECT @loop_counter = COUNT(*) FROM @item_table
SET @item_counter = 1
SET @result = ”
WHILE @loop_counter > 0 AND @item_counter <= @loop_counter
BEGIN
SELECT @item = item
FROM @item_table
WHERE primary_key = @item_counter
SET @result = @result + ”” + @item + ”’,’
SET @item_counter = @item_counter + 1
END
SET @result = LEFT(@result,LEN(@result)-1) — trim last comma
return @result
END
To test the above function, we can use the following script:
declare @result varchar(1000)
set @result = dbo.ParmsToINValues(‘1001,2323,jag,chat,3232’)
print ‘select * from emp where empid in (‘ + @result + ‘)’
The resulting output would be as follows:
select * from emp where empid in (‘1001′,’2323′,’jag’,’chat’,’3232′)
Hope the post is useful….
Thanks
Jag
Jagadish Pulakhandam's 