How to capture return value of sproc?

Question

I have a sproc, which I can't modify the signature of, that has a SELECT statement as its last line:

SELECT userid = @userid

where @userid is declared inside the sproc (not passed in). Is there a way to capture the value of userid in a variable outside of the sproc?

If I want to capture two values by modifying the last line:

SELECT userid = @userid, somevar = @somevar

will that affect any existing code paths that are using this sproc (say calls being made in .NET)? If not, how do I capture two values outside of the sproc?

These captures will all be done in SQL, not an application language.

I'm missing something here, what is your .net code doing with this. return from a proc, ExecuteScalar? — Tony Hopkinson, Jan 03 '13 at 22:30
Please see [How to SELECT * INTO temp table FROM stored procedure](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — Michael Fredrickson, Jan 03 '13 at 22:34

score 2 · Accepted Answer · answered Jan 03 '13 at 22:31

A stored procedure that does a SELECT like that isn't actually sending back return values, it's sending back a result set. One way to capture that in your SQL would be to insert the results into a temp table.

CREATE TABLE #MyTemp 
  (UserId INT, 
  SomeVar VARCHAR(50))

INSERT #MyTemp
  EXEC MySproc

At this point you can SELECT from #MyTemp into local variables.

Whether adding another column to the result will break any existing application code is really a big "it depends."

How to capture return value of sproc?

1 Answers1