Click here to Skip to main content
Click here to Skip to main content
Go to top

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)

Share

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
Web03 | 2.8.140922.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