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
430 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS 2015 is giving [No relevant source lines] 2 39
SSMS Imprt data from Excel 7 25
Build a string of emails from a gridview 2 16
c#, datatable, aspx 4 20
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
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.

749 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