Introduction
This article explains how to pass an array into a Stored Procedure. I have provided the fully functional source code, which is self-explanatory.
Background
After reading this CodeProject article, I tried the same using the Image
data type, but I faced a lot of issues while implementing it in my project. So I tried again using string, but it will only accept a single dimension array. The trick then is to convert the list of values into a string separated by commas and pass the string into a Stored Procedure, where we convert the string into a table and then iterate through the table to get the list values.
Using the Code
This sample explains how to handle arrays in SQL Server. For this, I wrote a Stored Procedure called "Sample_Array_Handling
" and a function "Split
". The Stored Procedure accepts a string parameter [array items separated by comma] and prints all the array items.
Steps
- Pass the array as a string, each array item separated by a ','.
- Split the string using the '
Split
' function. - Create a temporary table and insert the resultset of step 2 into the table.
- Finally, use a cursor to iterate through the table rows and get each array item.
Sample: Sample_Array_Handling '1,2,3'
:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sample_Array_Handling]
@SampleArray nvarchar(10)
AS
BEGIN
Create table #tempArrayTable (rid varchar(500))
Insert into # tempArrayTable (rid)
(select value from dbo.Split(@SampleArray,','))
DECLARE @ArrayItem nvarchar(100)
DECLARE @Array_Cursor CURSOR
SET @Array_Cursor = CURSOR FAST_FORWARD FOR select rid
from # tempArrayTable
OPEN @ Array_Cursor
FETCH NEXT FROM @ Array_Cursor INTO @ArrayItem
WHILE @@FETCH_STATUS = 0
BEGIN
print @ArrayItem
FETCH NEXT FROM @ Array_Cursor INTO @ArrayItem
END
Close Array_Cursor
deallocate Array_Cursor
END
The code:
CREATE FUNCTION [dbo].[Split](@sText varchar(8000), @sDelim varchar(20) = '
')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx int,
@value varchar(8000),
@bcontinue bit,
@iStrike int,
@iDelimlength int
IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
if(Len(@sText) = 0)
return
IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END
Points of Interest
You might have come across situations where you wanted to split strings or create a table from a list of values. In such situations, you can use the Split
function I have attached with this article.
In my solution, I have used a cursor for fetching values from a table. You can avoid the usage of the cursor; for more information, refer this article.