SP_ExecuteSQL Generic stored_procedure call without output parameters, but catching the output

Question

I'm scratching my head hard on this pb and I would like some help to figure out some solution.

Inside some TSQL programmable object (a stored procedure or a query in Management Studio)

I have a parameter containing the name of a stored procedure + the required argument for these stored procedures (for exemple it's between the brackets [])

Sample of @SPToCall

EX1 : [sp_ChooseTypeOfResult 'Water type']
EX2 : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : [sp_GetSomeResult]

I can't change thoses stored procedures (and I don't have a nice output param to cache, as I would need to change the stored procedure definition)

All these stored procedures 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param in those stored procedures definition (it would have been too easy otherwise :D)

So I'm working on something like this but I can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput  NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult ''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

exec SP_executeSQL @SPToCall , @paramsDefinitions, @CatchedOutput = @myFinalVarFilledWithCachedOutput OUTPUT

-- my goal is to get something inside  @myFinalVarFilledWithCachedOutput  

Select @myFinalVarFilledWithCachedOutput 

Any ideas ?

Answer 1

Here's an example of the syntax to take output of stored procedure that returns a selected value and pass that output to a table variable.

CREATE PROCEDURE tester
AS
    BEGIN
        DECLARE @var VARCHAR(10)
        SET @var = 'beef'

        SELECT  @var
    END
GO


DECLARE @outputtab TABLE ( varfield VARCHAR(100) )

INSERT  @outputtab
        ( varfield )
        EXEC tester

GO

SELECT  *
FROM    @outputtab

From there if you want to get it into a varchar variable:

DECLARE @outputvar VARCHAR(100)

SET @outputvar = ( SELECT TOP 1
                            *
                   FROM     @outputtab
                 )