After reading the codeproject article,
I have tried the same using image datatype, but i faced lot of issues
while implementing the same in my project. So i implemented the same
using string, but this will accept
single dimension array only. Here
the trick is to convert the list of values into string separated by
commas
and pass the string into stored procedure, Where we convert the
string into 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 have wrote a stored procedure called
“
Sample_Array_Handling“ and a function “Split”. Stored procedure accepts
a string parameter [array items separated by comma] and prints all
array items.
Steps:
1.Pass the array as string, each array items are separated by ‘,’.
2.Split the string using ‘Split’ function.
3.Create a temporary table and insert the resultset of the step2 into the table.
4.Finally, Use cursor to iterate through the table rows and get each array item.
Sample : Sample_Array_Handling ‘1,2,3′
Code:
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
String Split Function :
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
You
might have come across situation where you want split strings or create
table from a list of values.
In such situations, you can use the Split
function i have attached with this article.
In this thread, i have used cursor for fetching values from table. You can avoid usage of cursor, for more informations refer this article
For attachments, Please refer this link My code4asp.net forum post or codeproject article