sql server - SP_ExecuteSQL Generic stored_procedure call without output parameters, but catching the output -

i'm scratching head hard on pb , figure out solution.

inside tsql programmable object (a stored procedure or query in management studio)

i have parameter containing name of stored procedure + required argument these stored procedures (for exemple it's between 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 don't have nice output param cache, need change stored procedure definition)

all these stored procedures 'return' 'select' of 1 record same datatype ie: nvarchar. unfortunately there no output param in stored procedures definition (it have been easy otherwise :d)

so i'm working on can't find 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  -- goal inside  @myfinalvarfilledwithcachedoutput    select @myfinalvarfilledwithcachedoutput  

any ideas ?

here's example of syntax take output of stored procedure returns selected value , pass output table variable.

create procedure tester     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  *    @outputtab 

from there if want varchar variable:

declare @outputvar varchar(100)  set @outputvar = ( select top 1                             *                        @outputtab                  )