SQL Server中存储过程调用时自动触发日志存储过程的实现问询

Great question! SQL Server doesn't have a native "hook" mechanism that automatically triggers a logging stored procedure every time another proc is called, but there are a few workarounds you can consider depending on your needs:

1. 间接非实时方案：扩展事件 + SQL Agent作业 #

• 你可以创建一个扩展事件会话，捕获rpc_completed（远程过程调用）或sp_statement_completed（临时执行存储过程）这类事件，再通过筛选条件只保留你关注的存储过程调用记录。
• 把事件数据导出到文件目标，接着创建SQL Agent作业按计划读取这些数据，调用日志存储过程完成日志写入。
• 优势：不需要修改现有存储过程。
• 劣势：不是实时触发（延迟取决于作业调度频率），需要维护扩展事件会话和Agent作业，且筛选逻辑可能需要调整以避免捕获无关事件。

2. 接近实时方案：事件通知 + Service Broker #

• 事件通知可以捕获存储过程执行相关的事件（比如RPC:Completed或SQL:BatchCompleted），并将消息发送到Service Broker队列中。
• 你可以创建一个激活存储过程，当队列中有消息到达时自动触发，进而调用日志存储过程记录执行信息。
• 优势：接近实时触发，无需直接修改业务存储过程。
• 劣势：配置Service Broker和事件通知有一定学习成本，需要处理消息可靠性（比如死信队列），部分边缘场景（如嵌套存储过程调用）可能需要额外的筛选逻辑。

3. 最可靠方案：批量修改现有存储过程 #

• 如果你能接受修改存储过程，这是最直接且可靠的方式。可以自动化给所有现有存储过程添加日志调用语句，后续新建存储过程也统一遵循这个规范。
• 利用系统视图自动生成ALTER脚本，示例如下：

SELECT 
    'ALTER PROCEDURE ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ' ' + m.definition + 
    CHAR(13) + CHAR(10) + '-- 自动添加日志调用' + CHAR(13) + CHAR(10) + 'EXEC dbo.YourLoggingProcedure @ProcedureName = ''' + s.name + '.' + p.name + ''';'
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P'

• 仔细检查生成的脚本后再执行，后续新建存储过程时使用包含日志调用的模板即可。
• 优势：实时日志记录，实现和维护简单，无需额外基础设施。
• 劣势：需要修改现有存储过程（但可自动化完成），且要确保新存储过程遵循相同规范。

总结 #

如果你绝对需要对现有存储过程零修改的方案，可以选择事件通知+Service Broker的组合（若能接受延迟，扩展事件方案也可行）。但对于大多数场景，批量修改存储过程的方式是最简单、最可靠的选择。

内容的提问来源于stack exchange，提问作者Poppert