how do u return values as array from a stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how do u return values as array from a stored proc

Author

Topic

nellyihu
Starting Member

8 Posts

Posted - 2005-06-15 : 12:56:24

pls how do u return values as array from a stored procedure i have a procedure that does it but only returns the first value out of more that it is supposed to return.

the procedure takes a csv and the loops round the csv and return a csv back based on certain conditions.
here's the code


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   procedure sms_checkphonebook 
(
   @csv as Varchar(8000)

)
AS
 
 --declarations
 declare @Delimter Char(1)
 declare @each_number Varchar(8000)
 declare @counter int
 declare @Csv_Position int
 declare @return_numbers  varchar(8000)
 --
 set @counter = 1
 set @delimter = ','

 --add a trailing delimiter to the array
 select @csv = @csv + @Delimter
 
 --create a temporary table to hold each element
 WHILE (@counter >= 1)
 BEGIN
	 select @Csv_Position = CHARINDEX(@Delimter, @csv)
	 select @each_number = Rtrim(Ltrim(SUBSTRING(@csv,1, @Csv_Position -1)))
	 
	
	 IF Not Exists(Select PhoneNumber From SMS_PHONEBOOK Where PhoneNumber =@each_number)
	    BEGIN
		SELECT @return_numbers=@return_numbers + @each_number
		Return @each_number
	     END
	
	 
	 select @csv = SUBSTRING(@csv, @Csv_Position + 1, DATALENGTH(@csv))
	 select @counter = CHARINDEX(@Delimter, @csv)
 END

return @return_numbers


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

nellysoft

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-15 : 13:21:07

SQL Server doesnt really a concept of an "array."

If I understand correctly, you just want to input a CSV of phone numbers and return only the non-existent ones... correct? I assume you want to retain the CSV format. If you need the values in an array-like table, you can use a table variable and return the resultset. See BOL.

The CSV method is as such. Dont use the return. Simply select it (as below) or use an output parameter. And you must initialize the variable from NULL to '', otherwise you are just saying (NULL + X) = NULL rather than the desired ('' + X) = X.

Let me know if this works for you:

alter   procedure sms_checkphonebook 
(
   @csv as Varchar(8000)

)
AS
 
 --declarations
 declare @Delimter Char(1)
 declare @each_number Varchar(8000)
 declare @counter int
 declare @Csv_Position int
 declare @return_numbers  varchar(8000)
 --
 set @counter = 1
 set @delimter = ','

 --add a trailing delimiter to the array
 select @csv = @csv + @Delimter

 --create a temporary table to hold each element
 WHILE (@counter >= 1)
 BEGIN
	 select @Csv_Position = CHARINDEX(@Delimter, @csv)
	 select @each_number = Rtrim(Ltrim(SUBSTRING(@csv,1, @Csv_Position -1)))
	 -- print @each_number
	
	 IF Not Exists(Select PhoneNumber From SMS_PHONEBOOK Where PhoneNumber = @each_number)
	    BEGIN
		SELECT @return_numbers = coalesce(@return_numbers + ', ', '') + @each_number
		--Return @each_number
	     END
	
	 select @csv = SUBSTRING(@csv, @Csv_Position + 1, DATALENGTH(@csv))
	 select @counter = CHARINDEX(@Delimter, @csv)
 END

select @return_numbers

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-15 : 13:32:44

The table variable solution would look like this:

alter   procedure sms_checkphonebook 
(
   @csv as Varchar(8000)

)
AS
 
 --declarations
 declare @Delimter Char(1)
 declare @each_number Varchar(8000)
 declare @counter int
 declare @Csv_Position int
 declare @return_numbers  varchar(8000)
 --
 set @counter = 1
 set @delimter = ','

-- add a trailing delimiter to the array
SELECT @csv = @csv + @Delimter

-- create table varible for results
DECLARE @number table (number_id int identity(1,1), number_value varchar(10))

 --create a temporary table to hold each element
WHILE (@counter >= 1)
BEGIN
	SELECT @Csv_Position = CHARINDEX(@Delimter, @csv)
	SELECT @each_number = Rtrim(Ltrim(SUBSTRING(@csv,1, @Csv_Position -1)))
	
	IF Not Exists(Select PhoneNumber From SMS_PHONEBOOK Where PhoneNumber = @each_number)
	BEGIN
		INSERT INTO @number (number_value)
		SELECT @each_number
	
	END
	
	select @csv = SUBSTRING(@csv, @Csv_Position + 1, DATALENGTH(@csv))
	select @counter = CHARINDEX(@Delimter, @csv)
END

select number_id, number_value 
from @number

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-15 : 17:28:05

nicely done nathans..

now answer my post/thread LOL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources