title: Dynamically iterate through passed in parameter-value(s) in T-SQL procedure
body: <p>I'm currently trying to write a default procedure template for reporting from a T-SQL Datawarehouse.</p>
  
  <p>The idea is to wrap each query in a procedure, so that permissions and logging can be managed easily.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>The closest to a solution I've come, is this minimal example:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>However, this does have to major drawbacks.</p>
  
  <ol>
  <li>It doesn't work due to variable scopes</li>
  <li>It is prone to SQL-Injections, which is unacceptable</li>
  </ol>
  
  <p>Is there any way to solve this issue?</p>