Solved

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

Posted on 2008-10-07
3
423 Views
Last Modified: 2008-10-14
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~
0
Comment
Question by:salman_sulaiman_2008
3 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 22666640
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
 

Author Comment

by:salman_sulaiman_2008
ID: 22666738
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
 
LVL 11

Accepted Solution

by:
saleek earned 300 total points
ID: 22666798
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now