title: What is the best way to assign the returned value of a stored proc to a variable in SQL?
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Currently I have the following and I'm wondering if its very inefficient?</p>
  
  <pre><code>DECLARE @tmpNewValue TABLE (newvalue int)
  INSERT INTO @tmpNewValue EXEC GetMyValue
  
  DECLARE @localVariable int
  SET @localVariable = (SELECT TOP 1 newvalue FROM @tmpNewValue )
  </code></pre>
  
  <p>Isn't there a more straight forward way of doing this? Isn't this an expensive (in terms of performance) way?</p>
  
  <p>My stored proc doesn't have an output param, it just returns a value. Would using an output param be faster?</p>
  
  <p>For what it's worth I'm using MS SQL Server 2005</p>