如何准备存储过程参数的键值表?

huangapple go评论71阅读模式
英文:

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

问题

我正在为处理具有不同参数数量的数据的过程准备模板。长话短说,我想将HTML表格作为TRY CATCH块的输出返回。

非常简化的模板:

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

AS

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

从运行_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>'

屏幕截图:https://ibb.co/2vVTXt9

我尝试过的方法:

  1. 在每个过程中手动创建表格 - 目前对于5个过程来说可以工作,但当其他人使用它时,在40多个过程中难以维护。

  2. SELECT * FROM sys.parameters INNER JOIN sys.procedures ON parameters.object_id = procedures.object_id WHERE procedures.name = OBJECT_NAME(@@PROCID) - 返回参数名称列表,但我不知道如何检索其值。

  3. select * from sys.dm_exec_input_buffer(@@spid, current_request_id()) 给我原始数据转储,但很难格式化。

是否有类似Python argv的东西?

英文:

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) = &#39;&#39;
 /* Code */
 SELECT @DATAINPUT

Expected value from running _Test &#39;123&#39;, &#39;246&#39;, &#39;NN&#39;:

@DATAINPUT = &#39;&lt;tr&gt;&lt;td&gt;@VALUE1&lt;/td&gt;&lt;td&gt;123&lt;/td&gt;&lt;/tr&gt;
              &lt;tr&gt;&lt;td&gt;@VALUE2&lt;/td&gt;&lt;td&gt;246&lt;/td&gt;&lt;/tr&gt;
              &lt;tr&gt;&lt;td&gt;@VALUEN&lt;/td&gt;&lt;td&gt;NN&lt;/td&gt;&lt;/tr&gt;&#39;

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?

答案1

得分: 0

-- 回答我的问题:

-- 获取完整的执行语句
DECLARE @statement nvarchar(max)
SELECT  @statement = event_info
FROM    sys.dm_exec_input_buffer(@@spid, current_request_id())

-- 从语句中解析参数值
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, '&#39;,&#39;)

UPDATE @Table SET value = REPLACE(value, '&#39;&#39;&#39;&#39;', '&#39;&#39;')

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

免责声明:
这部分是另一位用户回答中的代码,我想给予认可,但我找不到那个帖子。无论如何 - 非常感谢匿名编程者。

英文:

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, &#39;,&#39;)

UPDATE @Table SET value = REPLACE(value, &#39;&#39;&#39;&#39;, &#39;&#39;)

DECLARE @Output nvarchar(max)
SELECT @Output = isnull(@Output + &#39;&#39;,&#39;&#39;) + IIF(p2.seq % 2 = 1, &#39;&lt;tr class=&quot;odd&quot;&gt;&#39;, &#39;&lt;tr&gt;&#39;)+&#39;&lt;td&gt;&#39; + p1.name + &#39;&lt;/td&gt;&lt;td&gt;&#39;+convert(varchar(512),ISNULL(ltrim(p2.value) , &#39;NULL&#39;)) + &#39;&lt;/td&gt;&lt;/tr&gt;&#39;
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.

huangapple
  • 本文由 发表于 2023年7月6日 16:24:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76626876.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定