[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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~
0
salman_sulaiman_2008
Asked:
salman_sulaiman_2008
2 Solutions
 
Guy Hengel [angelIII / a3]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
 
Ramesh SrinivasTechnical 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now