Dynamically iterate through passed in parameter-value(s) in T-SQL procedure

Question

I'm currently trying to write a default procedure template for reporting from a T-SQL Datawarehouse.

The idea is to wrap each query in a procedure, so that permissions and logging can be managed easily.

Since this will be done by the DBAs, I would like to have this solution work by only pasting some standard code before and after the main query. I'd prefer if the DBA didn't have to modify any part of the logging-code.

I've solved this for most parts, however, I need to log which parameters the user has submitted to the procedure. The obvious solution would be hardcode the parameters into the logging. However, the procedures can have a varying amount of parameters, and I'd therefore like a catch-all solution.

My understanding is that there is no easy way iterating through all parameters. I can however access the parameter-names from the table sys.parameters.

The closest to a solution I've come, is this minimal example:

CREATE TABLE #loggingTable (
  [ProcedureID] INT
, [paramName] NVARCHAR(128)
, [paramValue] NVARCHAR(128)
)
;
go

CREATE PROCEDURE dbo.[ThisIsMyTestProc] (
        @param1 TINYINT = NULL
      , @Param2 NVARCHAR(64) = null
)
AS
BEGIN
   -- Do some logging here

   DECLARE @query NVARCHAR(128)
   DECLARE @paramName NVARCHAR(128)
   DECLARE @paramValue nvarchar(128)

   DECLARE db_cursor CURSOR FOR 
   SELECT [name] FROM [sys].[parameters] WHERE object_id = @@PROCID

   OPEN db_cursor 
   FETCH NEXT FROM db_cursor INTO @paramName

   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @query = 'SELECT @paramValue = cast(' + @paramName + ' as nvarchar(128))';
      SELECT @query;
      -- Following line doesn't work due to scope out of bounds, and is prone to SQL-Injections.
      --EXEC SP_EXECUTESQL @query; -- Uncomment for error
      insert into #loggingTable(ProcedureID, paramName, paramValue)
      values(@@PROCID, @paramName, @paramValue)
      FETCH NEXT FROM db_cursor INTO @paramName
   END

   CLOSE db_cursor
   DEALLOCATE db_cursor

   -- Run the main query here (Dummy statement)
   SELECT @param1 AS [column1], @Param2 AS [column2]

   -- Do more logging after statement has run

END
GO

-- test
EXEC dbo.[ThisIsMyTestProc] 1, 'val 2';
select * from #loggingTable;

-- Cleanup 
DROP PROCEDURE dbo.[ThisIsMyTestProc];
DROP table #loggingTable;

However, this does have to major drawbacks.

1. It doesn't work due to variable scopes
2. It is prone to SQL-Injections, which is unacceptable

Is there any way to solve this issue?

Answer 1

I'd like to use a simpler statement to generate the log info:

select 'select '+STUFF(
(select ',' + name+' AS [' + name+']' 
from sys.parameters where OBJECT_NAME(OBJECT_ID)='uspxxx'
order by parameter_id       FOR XML path('')
), 1,1, '')+' FOR XML PATH(''Parameters'')'

Answer 2

The values of the parameters are not availiable in a generic approach. You can either create some code generator, which will use sys.parameters to create a chunk of code you'd have to copy into each of your SPs, or you might read this or this about tracing and XEvents. The SQL-Server-Profiler works this way to show you statements together with the parameter values...

If you don't want to get into tracing or XEvents you might try something along this:

--Create a dummy proc

CREATE PROCEDURE dbo.[ThisIsMyTestProc] (
        @param1 TINYINT = NULL
      , @Param2 NVARCHAR(64) = null
)
AS
BEGIN
    SELECT @@PROCID; 
END
GO

--call it to see the value of @@PROCID

EXEC dbo.ThisIsMyTestProc; --See the proc-id
GO

--Now this is the magic part. It will create a command, which you can copy and paste into your SP:

  SELECT CONCAT('INSERT INTO YourLoggingTable(LogType,ObjectName,ObjectId,Parameters) SELECT ''ProcedureCall'', ''',o.[name],''',',o.object_id,','
         ,'(SELECT'
         ,STUFF((
            SELECT CONCAT(',''',p.[name],''' AS [parameter/@name],',p.[name],' AS [parameter/@value],''''')
            FROM sys.parameters p 
            WHERE p.object_id=o.object_id
            FOR XML PATH('')
          ),1,1,'')
          ,' FOR XML PATH(''''),ROOT(''parameters''),TYPE)'
          ) 
   FROM [sys].[objects] o 
   WHERE o.object_id = 525244926; --<-- Use the proc-id here

--Now we can copy the string into our procedure
--I out-commented the INSERT part, the SELECT is enough to show the effect

ALTER PROCEDURE dbo.[ThisIsMyTestProc] (
        @param1 TINYINT = NULL
      , @Param2 NVARCHAR(64) = null
)
AS
BEGIN
    --The generated code comes in one single line
    --INSERT INTO YourLoggingTable(LogType,ObjectName,ObjectId,Parameters) 
    SELECT 'ProcedureCall'
          ,'ThisIsMyTestProc'
          ,525244926
          ,(SELECT'@param1' AS [parameter/@name],@param1 AS [parameter/@value],''
                 ,'@Param2' AS [parameter/@name],@Param2 AS [parameter/@value],'' 
            FOR XML PATH(''),ROOT('parameters'),TYPE)
END
GO

Hint: We need the empty element (,'') at the end of each line to allow multiple elements with the same name.

--Now we can call the SP with some param values

EXEC dbo.ThisIsMyTestProc 1,'hello'; 
GO

As a result, your Log-Table will get an entry like this

ProcedureCall   ThisIsMyTestProc    525244926   <parameters>
                                                  <parameter name="@param1" value="1" />
                                                  <parameter name="@Param2" value="hello" />
                                                </parameters>

Just add typical logging data like UserID, DateTime, whatever you need...

Answer 3

Scope is the killer issue for this approach. I don't think there's a way to reference the values of parameters by anything but their variable names. If there was a way to retrieve variable values from a collection or by declared ordinal position, it could work on the fly.

I understand wanting to keep the overhead for the DBAs low and eliminating opportunities for error, but I think the best solution is to generate the required code and supply it to the DBAs or give them a tool that generates the needed blocks of code. That's about as lightweight as we can make it for the DBA, but I think it has the added benefit of eliminating processing load in the procedure by turning it into a static statement with some conditional checking for validity and concatenation work. Cursors and looping things should be avoided as much as possible.

Write a SQL script that generates your pre- and post- query blocks. Generate them in mass with a comment at the top of each set of blocks with the stored procedure name and hand it to the DBAs to copy/paste into the respective procs. Alternatively, give them the script and let them run it as needed to generate the pre- and post- blocks themselves.

I would include some checks in the generated script to help make sure it works during execution. This will detect mismatches in the generated code due to subsequent modifications to the procedure itself. We could go the extra mile and include the names of the parameters when the code is generated and verify them against sys.parameters to make sure the parameter names hard-coded into the generated code haven't changed since code generation.

-- Log execution details pre-execution
IF object_name(@@PROCID) = 'ThisIsMyTestProc' AND (SELECT COUNT(*) FROM [sys].[parameters] WHERE object_id = @@PROCID) = 2
BEGIN
    EXEC LogProcPreExecution @Params = CONCAT('parm1: ', @param1, ' parm2: ', @Param2), @ProcName = 'ThisIsMyTestProc', @ExecutionTime = getdate() @ExecutionUser = system_user
END
ELSE 
BEGIN
    --Do error logging for proc name and parameter mismatch
END

--Log procedure would look like this
CREATE PROCEDURE
    LogProcPreExecution
    @Parameters varchar(max),
    @ProcName nvarchar(128),
    @ExecutionTime datetime, 
    @ExecutionUser nvarchar(50)
AS
BEGIN
    --Do the logging
END