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