SQL Server Stored Procedure capture return value in T-SQL

Question

I have a SQL Server stored procedure; I need to capture the return value from the stored procedure. Is this the correct way of doing this?

  declare valback varchar(30)
  set valback = exec storeproc1 

In this case, storeproc1 is my stored procedure.

Answer 1

To start, use proper T-SQL syntax:

declare @valback int;
exec @valback = storeproc1;

The only return type allowed for a stored procedure is int. Stored procedures return status via the return statement.

I somehow have a feeling that you really want something else, namely: to have an OUTPUT parameter in the procedure:

declare @valback varchar(30);
exec storedproc1 @valback OUTPUT;

or capture the procedure result set via INSERT ... EXEC. See How to Share Data Between Stored Procedures.

Answer 2

The correct syntax is:

DECLARE @valback VARCHAR(30) 
EXEC @valback = storeproc1  

As per the documentation:

http://msdn.microsoft.com/en-us/library/ms188332.aspx