Can I pass an array of integers as a paramter for a stored procedure in SQL SERVER 2005??

Dear Experts!
Is there a way I could pass an array of integers as a paramters to a stored prcedure in sql server 2005, I guess my problem is how to delcare an array in sql server stored procedure and how to handle it in my sql statement. The reason why I need to do this is because in my Web application I need to make the user select a number of records from a gridview, and I would need to send those records' "record_Id" to the stored procedure, and the stored procedure will then carry out certain updates on those records. So I am thinking passing an array such as:
Dim records_id(10) as integer

if I can do this, then it would make it very easy for me to carry out the updates on those required records in one go.
I hope someone could help or direct me to the right path.

Thanks in advance.

~salman~
salman_sulaiman_2008Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ramesh SrinivasConnect With a Mentor Technical ConsultantCommented:
Hi,

Further to Angel's advise it is very usefull to have the split function as a "Table-valued function" within your db so you can easily use over and over.

I have attached the code for the function (probably from Angel but I have no recollection!).

To then, for example, filter a query by a string of comma separated integers, you would use in the where clause like so:

WHERE     (SomeID IN (select CONVERT(int, value)
                                 from dbo.fn_Split(@MyCommaSepInts,',')))

regards,

saleek
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint
 
IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END
 
SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1
 
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 cant 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

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
short answer: no.
long answer: yes, but not without some work.

you will have to use a single value somehow, for example xml-formatted or comma-delimited string with the list of values, and the stored procedure will then have to "split" up that list and do the work.

for the comma-delimeted "splitting", search for dbo.ParmsToList function on EE, you should find my code over and over again for that function.
0
 
salman_sulaiman_2008Author Commented:
Greetings Angelll,
thanks for the reply! I will search for it and give it a go, I have done string splitting before in my web applications, so I am thinking I should be fine doing it too in sql server.
Thanks angel.
Best wishes,
~Salman~
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.