t-sqlwindows-server-2016

How to prepare key - value table of stored procedure arguments?

I’m preparing template for procedures with different number of arguments that process data. Long story short I’d like to return a HTML table as an output for TRY CATCH block.

Very simplified template:

 CREATE PROCEDURE [dbo].[_Test]
 @VALUE1 nvarchar(10),
 @VALUE2 nvarchar(30),
 --...
 @VALUEN  nvarchar(MAX)

AS

 DECLARE @DATAINPUT NVARCHAR(MAX) = ''
 /* Code */
 SELECT @DATAINPUT

Expected value from running _Test '123', '246', 'NN':

@DATAINPUT = '<tr><td>@VALUE1</td><td>123</td></tr>
              <tr><td>@VALUE2</td><td>246</td></tr>
              <tr><td>@VALUEN</td><td>NN</td></tr>'

Screen: https://ibb.co/2vVTXt9

What I tried:

  1. Creating a table manually in each procedure - it works for now with 5 procedures, but it will be difficult to maintain in 40+ when other people will work with it

  2. SELECT * FROM sys.parameters INNER JOIN sys.procedures ON parameters.object_id = procedures.object_id WHERE procedures.name = OBJECT_NAME(@@PROCID) - returns list of argument names, but I don't how to retrieve its values

  3. select * from sys.dm_exec_input_buffer(@@spid, current_request_id()) gives me raw data dump, but it is difficult to format

Is there something like Pyhonian argv?

Solution

  • Answering my own question:

    -- get the full execution statement
DECLARE @statement nvarchar(max)
SELECT  @statement = event_info
FROM    sys.dm_exec_input_buffer(@@spid, current_request_id())

-- parse param values from the statement
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)

DECLARE @Table table(value varchar(max), seq int)

INSERT INTO @Table select  value, row_number() OVER (order by current_timestamp) seq from string_split(@params, ',')

UPDATE @Table SET value = REPLACE(value, '''', '')

DECLARE @Output nvarchar(max)
SELECT @Output = isnull(@Output + '','') + IIF(p2.seq % 2 = 1, '<tr class="odd">', '<tr>')+'<td>' + p1.name + '</td><td>'+convert(varchar(512),ISNULL(ltrim(p2.value) , 'NULL')) + '</td></tr>'
FROM sys.parameters p1 left join @Table p2 on p2.seq = parameter_id where object_id = @@procid

    Disclaimer: This is partially code from another user's answer that I'd like to give recognition, but I can't find the thread. Anyway - thank You anonymous coder.