What is the best way to assign the returned value of a stored proc to a variable in SQL?

Question

I have a stored procedure that returns a valueI call it from other stored procedures that need to retrieve this value. The calling stored procedure is inside a transaction, the stored procedure that returns the value (and actually creates the value and stores it in a table that no other proc touches) is not inside its own transaction, but would be part of the caller's transaction.

The question is this, what is the most efficient way of retrieving the return value of the stored procedure and storing it in a variable in the calling proc?

Currently I have the following and I'm wondering if its very inefficient?

DECLARE @tmpNewValue TABLE (newvalue int)
INSERT INTO @tmpNewValue EXEC GetMyValue

DECLARE @localVariable int
SET @localVariable = (SELECT TOP 1 newvalue FROM @tmpNewValue )

Isn't there a more straight forward way of doing this? Isn't this an expensive (in terms of performance) way?

My stored proc doesn't have an output param, it just returns a value. Would using an output param be faster?

For what it's worth I'm using MS SQL Server 2005

Answer 1

If your getting a single return variable then yes this is innefficent you can do:

declare @localVariable int
exec @localVariable =GetMyValue
select @localVariable

Answer 2

Is this proc returning a rowset of 1 row and 1 column or no rowset at all and you just want to capture the returncode?

If you want just the returncode then use Josh's method otherwise use a OUTPUT parameter sicne it will be much faster than what you are doing now

To Explain what I mean run this code

use tempdb
go

create proc GetMyValue
as
select 1
go


create table #temp (id int)
declare @localVariable int

insert #temp
exec @localVariable =GetMyValue
select @localVariable,* from #temp

Answer 3

See How to Share Data Between Stored Procedures
By some reasons 'exec @localVariable =GetMyValue' is not working for me (MS SQL 2005), it's always return 0 value (They have the same issue).

My opinion is:
if you can change stored procedure, add output parameter.
else if you can remove procedure, rewrite it as a function.
else use table variable, as you do.

Answer 4

create proc AvilableSeats as declare @v1 int,@v2 int exec @v1= determinPath_Capacity 1,'Sat-Tue',32 exec @v2=Student_fGroup '6/12/2009' select @v1-@v2