Is there anyway to reference the collection of parameters passed to a stored procedure, without referencing each one by name?

Question

Is there any way to reference the collection of parameters passed to a stored proc, without referencing each one by name?

The context here is error logging. I'd like to develop a generic CATCH clause, or sub-clause, that grabs all parameter values as well as other error and execution info and logs it to a table (one or more).

The basic version looks something like this:

CREATE PROC dbo.Proc1 @Param1 INT, @Param2 INT
AS
BEGIN TRY
  SELECT 1/0
END TRY
BEGIN CATCH
  DECLARE @params XML
  SET @params = (
    SELECT 
      @Param1 AS [Param1]
    , @Param2 AS [Param2]
    FOR XML PATH('params'), TYPE
    )
  EXEC dbo.LogError @procid = @@PROCID, @params = @params
  EXEC dbo.RethrowError
END CATCH

Now, this template does work, except that for each individual procedure, I would have to edit the SET @params section manually.

I could script it out easily enough, but then I would still need to copy it in.

I could leave some sort of placeholder, and then dynamically update each definition w/ a correct SET statement. I could even write a database trigger that listens for CREATE and ALTER PROC statements and have it do this for me automagically.

But what I really want is just to grab the collection of parameters directly and be done with it. Is there any way?

EDIT: getting parameter metadata from the system tables is not enough. For example:

DECLARE @sql NVARCHAR(MAX)
SET @sql = 
  'SET @params = (SELECT '
+ STUFF((
    SELECT ', '+name+' AS '+STUFF(name,1,1,'') 
    FROM sys.parameters WHERE object_id = @@PROCID
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
    ,1,2,'')
+ ' FOR XML PATH(''params''), TYPE)'
-- inline execute here....alas, there's isn't a mode for that
EXEC sp_executesql @sql, N'@params XML OUTPUT', @params OUTPUT

This, of course, generates an error, since I haven't declared parameters for the nested scope of sp_executesql. While I could script out the second param of sp_executesql, I can't assign them without explicitly assigning them one at a time by name.

Original question, rephrased: is there another flavor of sp_executesql that inherits variable "context" from the outer scope, that executes in the same frame, that inlines the command, rather than executing in a nested scope?

Answer 1

Why not use sys.dm_exec_sql_text(@sql_handle) to retrieve the SQL command, using sys.sysprocesses.sql_handle?