英文:
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
我尝试过的方法:
-
在每个过程中手动创建表格 - 目前对于5个过程来说可以工作,但当其他人使用它时,在40多个过程中难以维护。
-
SELECT * FROM sys.parameters INNER JOIN sys.procedures ON parameters.object_id = procedures.object_id WHERE procedures.name = OBJECT_NAME(@@PROCID)
- 返回参数名称列表,但我不知道如何检索其值。 -
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) = ''
/* 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:
-
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
-
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 -
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, '',')
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
免责声明:
这部分是另一位用户回答中的代码,我想给予认可,但我找不到那个帖子。无论如何 - 非常感谢匿名编程者。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论