Execute Stored Procedure from Stored Procedure w/ dynamic SQL capturing output

dynamic-sql, sql, sql-server, stored-procedures

Solution

You could use `sp_executesql` to dynamically call your nested Stored Procedure.

DECLARE @db AS SYSNAME
DECLARE @return_value AS INT
DECLARE @output_value AS INT
DECLARE @sql AS NVARCHAR(MAX)

-- Set your DB name
SET @db = N'mydb'

/*
Use sp_executesql to dynamically pass in the db and stored procedure
to execute while also defining the values and assigning to local variables.
*/
SET @sql = N'EXEC @rtn = ' + @db + '.dbo.[your_stored_procedure] @output OUTPUT'
EXEC sp_executesql @sql
   , N'@rtn AS INT, @output AS INT OUTPUT'
   , @return_value = @rtn
   , @output_value = @output OUTPUT

Problem

Inside a stored procedure (A) I need to call a stored procedure (X) inside a specific database and capture the output. X returns a single value. From what I understand I need to provide the DB name of X to the stored procedure in A and I need to use dynamic SQL to build the query on execution targeting the desired database. What am unable to figure out is how to capture output from X in A to work with the result.

Original source