CODEWITHSUNDEEP
sqlt-sqlstored-procedures
skomi
skomi

Reputation: 145

Dynamically get all parameter values in stored procedure

Is there any way to get all parameter values from a stored procedure dynamically?

In other words, iterate through all parameters in one stored procedure to get their values into one string. This is for a unified logging process for a bunch of stored procedures.

I can get the names of parameters:

SELECT PARAMETER_NAME 
FROM INFORMATION_SCHEMA.PARAMETER
WHERE SPECIFIC_NAME = 'procedure_name';

Also, I tried to use dynamic SQL commands. I've generated a command with included parameter, but EXEC can't execute command.

@cmd = 'SELECT '@UserID' + CONVERT(NVARCHAR(MAX), @UserID) 
             + '@Date' + CONVERT(NVARCHAR(MAX), @Date)'
EXEC @cmd

Is there any way to do this besides manually generating a list of parameter values for each stored procedure?

Upvotes: 3

Views: 2964

Answers (1)

egerardus
egerardus

Reputation: 11486

Since SQL Server 2014 there is sys.dm_exec_input_buffer a table valued function with an output column event_info that gives the full execution statement (including parameters).

I use this for error logging in stored procedures.

For example:

--include this inside the stored procedure
declare @statement nvarchar(max)
select  @statement = event_info 
from    sys.dm_exec_input_buffer(@@spid, current_request_id())

--this will print whatever you called the procedure with (including parameters)
print @statement

-- if you want to parse just the parameters from the statement, it can be done like this
declare @proc_name varchar(128) = object_name(@@procid)
declare @param_idx int = charindex(@proc_name, @statement) + len(@proc_name)
declare @param_len int = len(@statement) - @param_idx 
declare @params nvarchar(max) = right(@statement, @param_len)

select  @params

Upvotes: 3

Related Questions