how to return more than two values from a stored procedure..

Hi,

I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.

How can I do that. I'm able to get only 1 value if i use the return key word.

Eg:

create proc test1 as

Begin

Declare scount int

Declare scount2 int

-- statements in stored procedure

return scount

return scount2

End

create proc test2

Declare variables...

Exec Test1

// here i want the values (scount and scount2 ), processed in stored procedure Test1 .

How can i get this.. please let me know..

Thanks,

srikanth

I would suggest using a function rather than a stored procedure; however, it is also possible to do what you want using a stored procedure, but you will not be able to use the RETURN verb to return the values. Give a look to CREATE PROCEDURE in books online.

An inline function to return 2 values would look something like:

Code Snippet

create function dbo.test1 ()
returns table
as
return
( select 1 as scount, -- or whatever values
3 as scount2
)

The function would be invoked with something like:

Code Snippet

select scount, scount2

from test1()

Give a look to the CREATE function article in books online.

To doctor up your procedure, try something like:

Code Snippet

create proc test1
@.scount integer output,
@.scount2 integer output
as

-- statements in stored procedure

set @.scount = 14 -- or the appropriate value

set @.scount2 = 21 -- or the appropriate value

return

To invoke the stored procedure, try something like:

Code Snippet

declare @.out1 integer, out2 integer

exec @.test1 @.out1 = @.scount output,
@.out2 = @.sctount2 output

select @.out1 = [@.out1],
@.out2 = [@.out2]

|||

during the process i have to modify data in database.so i cant use functions as they cant update the data.

hence i hav chosen stored procedure.

let me know if there is a way in stored procedure itself..

thanks for ur reply..

|||

Which stored procedure is updating the data -- the stored procedure that invokes the other stored procedure or the stored procedure that gets invoked?

|||

You can use OUTPUT parameters.

In SQL Server 2005 perhaps you may use OUTPUT CLAUSE http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1642175&SiteID=1 for your purpose.

|||

hi Kent,

thanks for your information in your previous reply.

Its working the way as you said.

Thanks,

srikanth