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

Solution 1

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

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

Solution 2

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.

Solution 3

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

Comments

[ApplePieIsGood]

Interesting, didn't know that construct was valid. So the example I gave is creating a temp table. How inefficient would you describe it to be, very or it wouldn't be noticeable unless it was called tens of thousands of times?

[JoshBerke]

hard to say, your table is a table variable so it should all be in memory. Why not run a test on your system measure the difference btw the two?

[SQLMenace]

It will always be 0 unless you have a return in the proc or an error happens, run the code I provided

[ApplePieIsGood]

I am only interested in a return value. Will using Josh's method be slower or faster than an output param when all I want is to get that return value or a specific value (it doesn't have to be a return value, just a value I've created inside this proc). Seems either is faster I'm currently doing.