Click here to Skip to main content
Click here to Skip to main content

Array Parameter Handling in a Stored Procedure

, 11 Jun 2008
Rate this:
Please Sign up or sign in to vote.
How to handle an array parameter in a Stored Procedure [SQL Server].

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Srinath G Nath
Technical Lead Tata Consultancy Services
India India
Srinath has done B.Tech from AWH Engineering College ,Calicut , Kerala, India. Currently working in Tata Consultancy Services[Software MNC] as a Tech Lead @ Cochin, India. He loves to working with Microsoft Technology.
 
Language / Technology :C#,VB.NET, ASP.NET, AJAX, XML, ADO.Net , WinForms, Javascript, JQuery, PL/SQL, Web Services , SQL Server 2000 & 2005.


Application Server : IIS 5.1, IIS 6.


Certification : IBM Certified Solution Implementer - WebSphere DataPower and MCP Web Client Development


Srinath's Articles:
View My CodeProject Articles


Srinath's Site:
code4asp.net
www.talks4u.com
 


Comments and Discussions

 
Questionhello. I want to buy split string from Table Pinmemberelektroengin4-Jan-13 2:15 
Questioninsert unkown number of column value in table Pinmemberdeepanshu60000121-Sep-11 23:28 
GeneralNice Pinmemberreshi99916-Jun-08 22:57 
GeneralRe: Nice Pinmembersrinath g nath20-Jun-08 2:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 12 Jun 2008
Article Copyright 2008 by Srinath G Nath
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid