sql - Pass stored procedure parameter values to nested stored procedure without knowing parameter names -

i trying create universal error trapping stored procedure called within stored procedures , need parameter names (i can system tables knowing stored procedure name) , values.

the values need obtain , them dynamically (without having pass them stored procedure name).

is there way pass unique id or other thing nested stored procedure can use parameter values main stored procedure in nested stored procedure?

short example: have stored procedure:

create procedure testparamaters     @text1 varchar(500),     @datetime1 datetime  -- want able call sp this: exec dynamicerrortrackingsp (pass something, system variable/data/etc, can                               dynamically parameter values in nested sp) 

i have code similar , works fine (but have edited every sp correctly pass parameter values (i not want have edit every sp).

exec dynamicerrortrackingsp @parmaternamevaluepairs =       '@text1 = ' + @text1 + ', @datetime1 = ' + cast(@datetime1 varchar(500)) + '' 

tl;dr
if find way variable value it's name.

answer
possible create new data type of type table , use input of error tracking stored procedure. in variable there row each parameter name , value.
example of is

create type _stored_param table (   name varchar(50) , [value] sql_variant ) go  create procedure printparam (@sentparam _stored_param readonly) begin   select * @sentparam end go  create procedure myprocedure (@param varchar(10)) begin   declare @myparam _stored_param;    insert @myparam (name, [value]) values ('@param', @param)   exec printparam @myparam; end go  exec myprocedure 'foo' 

the result of execution of myprocedure, done last line you

name    value ------- ------- @param  foo 

cool! ... insert static, , every time stored procedure parameter changed or added insert need changed well.

we can change procedure create dynamic insert, using information_schema.parameters parameters name using object_name(@@procid) in where condition 1 current procedure.

an inception sp

create procedure inception (@param1 varchar(10), @param2 int) begin   declare @paramquery nvarchar(max);   declare @paramvar _stored_param;   declare @execvar nvarchar(max);    set @paramquery = '';   select   @paramquery          += 'select ''' + parameter_name           + ''', cast(' + parameter_name + ' sql_variant) union '       information_schema.parameters      specific_name = object_name(@@procid)   order ordinal_position    set @paramquery = left(@paramquery, len(@paramquery) - 10);    set @execvar = ''   select   @execvar          += parameter_name + ' ' + data_type           + isnull('(' + cast(character_maximum_length varchar) + '), ', ', ')       information_schema.parameters      specific_name = object_name(@@procid)   order ordinal_position    set @execvar = left(@execvar, len(@execvar) - 1);    insert @paramvar   exec sp_executesql @paramquery, @execvar, @param1, @param2    select * @paramvar end go  exec inception 'foo', 2; 

within stored procedure variable @paramquery evaluated string

select '@param1', cast(@param1 sql_variant)  union  select '@param2', cast(@param2 sql_variant) 

but dynamic query need executed.

executing dynamic query calling stored procedure: need pass parameter when needed , execution on isolated level own local parameter caller variable not visible.
means execute query parameters values need pass parameters values parameters.
advantage when stored procedure altered changing parameter number query generate error like

msg 8178, level 16, state 1, line 39 parameterized query '(@param1 varchar(10), @param2 int)select '@param1', cast(@param1' expects parameter '@param2', not supplied. 

asking check sp_executesql parameter passage.

other method creating string name , value of parameter or creating xml name , value of parameter hit same wall: value of variable given name string (varchar).