如何检索存储过程的输入参数值 [英] How to retrieve the input parameter values of a stored procedure

问题描述

我想创建一个审计过程，它将在我的数据库中所有过程的 catch 块中调用.

I want to create a auditing procedure which will be called in the catch block of all the procedure in my Database.

我想在这个审计数据库中存储所有输入参数及其值的列表.

I wanted to store the list of all the input parameters and its values in this auditing DB.

请建议我，如何在 SQL Server 中实现这一点

Please suggest me, how to achieve this in SQL Server

推荐答案

我不知道以编程方式检索存储过程的参数列表及其值(可能会涉及 n 个系统表和类似的东西).无需考虑那种复杂程度，并且如果可以更改当前程序，您可以按照以下方式进行操作.

I am not aware of programatically retrieving the list of parameters and their values for a stored proc(Possibly would involve n number of system tables and things like that). Without going into that level of complexity AND if altering the current procedures is a possibility, you could do something on the lines of below.

ALTER 现有的存储过程以添加一个小功能，您可以在其中使用
设置字符串格式的参数('@paramname = paramvalue')及其在当前过程中的值如果控制到达 CATCH 块，然后触发审计过程.

ALTER the existing stored procs to add a small functionality wherein you populate a table variable with
the parameters in a set string format('@paramname = paramvalue') and their values in the current proc and then fire the Auditing proc if the control reaches the CATCH block.

--Add this code bit on top of the proc from where you want the Audit Proc to be fired

--Declare and insert into a table variable
Declare @ParamValues TABLE (params varchar(400))

insert into @ParamValues
select '@id = '+ @id  UNION
select '@name  = '+ @name  UNION
select '@date = '+ @date

GO

...
....
END TRY

begin catch --Auditing proc code below
exec AuditDB.dbo.AuditProc @ParamValues, 
     OBJECT_NAME(@@PROCID) --this returns the name of current proc 
end catch

-------
-------Create the requisite SQL objects
-------
CREATE TABLE AuditDB.dbo.AuditTable
(
    AuditMessage varchar(400),
    ProcName varchar(200),
    DateTimeStamp DateTime
);
GO
CREATE TYPE AuditDB.dbo.ParamValuesType AS TABLE
(
    params varchar(400)
);
GO
CREATE PROCEDURE AuditDB.dbo.AuditProc
    @ParamValues dbo.ParamValuesType READONLY
    ,@ProcName varchar(200)
AS

BEGIN --Add whaterver lines of code required, this is just a basic version.
    INSERT INTO AuditDB.dbo.AuditTable      
    SELECT params, @ProcName, cast(getdate() as datetime) FROM @ParamValues
END;

这篇关于如何检索存储过程的输入参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！