How to set the result of exec storedproc to a variable?

Question

Hi all,

I need to exec a storeproc and store its scalar result to a local variable inside a storedproc.

How to implement?

E.G.

CREATE PROCEDURE [dbo].GetNthNo AS 
 DECLARE @a INT
 DECLARE @d INT
 DECLARE @n INT
DECLARE @S INT
SET @S=EXEC spGetNthNo @a,@d,@n

SELECT @S

Please help.

Answer 1

Try something like that

CREATE PROCEDURE Test
  @RetVal INT OUT
AS
BEGIN
  SET @RetVal = 99
END

DECLARE @X INT
EXEC Test @X OUT
PRINT @X

Edit: [comment following posting of T-SQL snippet in question]
You seem to need a wrapper, around the spGetNthNo Stored Procedure, maybe because this existing procedure doesn't return its result in the way that is desired. An alternative to a wrapper may simply be to modify [ever so slightly] spGetNthNo itself, so it works as desired in the first place (provided the method is not currently in use with its existing API).

Regardless of whether the changes will be in the original SP or in a wrapper, there are two distinct ways of retrieving data from an SP:

• With output variables (as shown above)
• By having the SP return a "recordset", i.e. a table made of rows (records) and columns (fields). (This is done by having a SELECT statement towards the end of the SP, as show in the question snippet)

With the output variable approach, the data is readily placed in the variables by the time the SP returns. With the recordset apporach, the calling logic needs to "consume" the data returned, in a similar fashion that it would from a SELECT statement.

Aside from the way the returned data is consumed, there are a few differences between these approaches.
The most obvious one is that the "recordset" approach allows the returning more values: one would have to explicitly declare say 30 variables (with some naming convention aimed at helping with the two-dimensional nature of the table) to emulate a the returns of a SP which "SELECT TOP 10 a, b, c FROM myTable". Also the SP would have to explicitly set each of the these output variables.
Another related but more subtle difference is that the recordset approach allows returning a number of rows and columns that is undefined at the time of the call. The number and types of the variables do not need to be expressed beforehand, rather they come with the metadata surrounding the recordset.

In short: the output variable approach is more suited to return a fixed set of a few variables, such as status code, maximum or minim value (or other aggregate values and calculations), or also, a few fields from a expected single record. The Recordset approach is used when the purpose of the stored procedure is to effectively provide a table-like result, or when it returns very many values, such as a long [and evolving] list of aggregate values, etc.

Answer 2

Instead of:

SET @S=EXEC spGetNthNo @a,@d,@n

You need:

EXEC @S = spGetNthNo @a,@d,@n

And then within the procedure, you need something like:

RETURN 100

or:

RETURN @x

for the value you want for @S after the procedure executes.

You can also use output parameters. Combined example:

IF OBJECT_ID('tempdb..#example') IS NOT NULL DROP PROCEDURE #example
GO

CREATE PROCEDURE #example
  @output_param INT OUTPUT
AS BEGIN
   SET @output_param = 100
   RETURN 200
END
GO

DECLARE @return INT, @param INT
EXEC @return = #example @output_param = @param OUTPUT
SELECT @return as [return value], @param as [output parameter]