Array Parameter Handling in a Stored Procedure

• Download source code - 1.5 KB

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

1. Pass the array as a string, each array item separated by a ','.
2. Split the string using the 'Split' function.
3. Create a temporary table and insert the resultset of step 2 into the table.
4. 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


-- =============================================
-- Author:        Srinath
-- Create date:     May 22 2008
-- Description:    Array Handling Sample
-- =============================================
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:

-- =============================================
-- To Split the string and returns a table
-- =============================================
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 you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
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
--One character remains.
--Insert the character, and exit the WHILE loop.
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.