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
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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