Automated Stored Procedure Parameter Logging in SQL Server
-- Create a test DB
CREATE DATABASE [testdb];
go
-- Switch the DB context
USE [testdb];
go
-- Create a basic logging table
CREATE TABLE dbo.sp_log
(
id BIGINT NOT NULL IDENTITY(1, 1),
proc_name NVARCHAR(50) NOT NULL,
execution_time DATETIME NOT NULL,
params NVARCHAR(max) NULL
);
go
-- Create a sample target SP that accepts a couple of parameters
CREATE PROCEDURE [dbo].[Test_proc] (@param1 VARCHAR(50),
@param2 VARCHAR(50))
AS
BEGIN
-- Whatever your other stored procedures do goes here.
-- Just selecting the params back out as an example.
SELECT @param1,
@param2;
END;
go
-- Create the helper proc to handle parsing of sp and its param values
CREATE PROCEDURE dbo.Sp_helper (@args NVARCHAR(max))
AS
BEGIN
-- Figure out where the SP name ends and the params begin
DECLARE @paramstartpos INT = Charindex('||', @args, 1) + 2;
-- Set the SP name
DECLARE @sp_name VARCHAR(50) = Substring(@args, 1, @paramstartpos - 3);
-- Set the param list
DECLARE @param_list NVARCHAR(max) = Substring(@args, @paramstartpos,
Len(@args) - @paramstartpos)
-- Declare a table to hold the params and populate it with the parsed values that were passed
DECLARE @params TABLE
(
parameter_id INT,
param_value NVARCHAR(255)
);
INSERT INTO @params
SELECT Row_number()
OVER (
ORDER BY (SELECT NULL)) AS parameter_id,
value AS parameter_value
FROM String_split(@param_list, '|');
-- Line up the expected params for the target SP with the values that were passed to the helper proc
DECLARE @paramvalues TABLE
(
sp_name VARCHAR(255),
param_id INT,
param_name VARCHAR(255),
param_value NVARCHAR(max)
);
INSERT INTO @paramvalues
SELECT pr.NAME AS sp_name,
p.parameter_id AS param_id,
pm.NAME AS param_name,
p.param_value
FROM sys.procedures pr
JOIN sys.parameters pm
ON pr.object_id = pm.object_id
JOIN @params p
ON p.parameter_id = pm.parameter_id
WHERE pr.NAME = @sp_name;
-- Put the params in a SQL string for logging and to be passed later during execution of the target SP
DECLARE @sqlparamlist NVARCHAR(max) = Stuff((SELECT ',' + (
param_name + '='
+ '''' +
param_value + '''' )
FROM @paramvalues
FOR xml path('')), 1, 1, '');
-- Log the execution
INSERT INTO dbo.sp_log
SELECT @sp_name,
Getdate() AS execution_time,
@sqlparamlist AS params;
-- Declare the target SP execution code with the proc name and passed params
DECLARE @sqlcmd NVARCHAR(max) = (SELECT
'exec dbo.' + @sp_name + ' ' + @sqlparamlist);
-- Execute the target code
EXEC Sp_executesql
@sqlcmd;
END;
go
-- Execute the sample SP with logging
EXEC dbo.Sp_helper
'test_proc||testing...|testing2...';
Comments
Post a Comment