How to capture input parameters from within stored procedure (SQL Server 2005)?

Question

I would like to create a generic logging solution for my stored procedures, allowing me to log the values of input parameters. Currently I am doing this more or less by hand and I am very unhappy with this approach. Ideally, I would like to say something like the following:

"given my spid, what are my input parameters and their values?"

This is the same information exposed to me when I run SQL Profiler -- the stored procedure's name, all input params and all input VALUES are listed for me. How can I get my hands on these values from within a stored procedure?

Thanks; Duncan

Answer 1

That is going to be difficult to do within a stored procedure. SQL profiler runs under a different SPID and runs a statement like this to capture the other users statements:

DECLARE @handle VARBINARY(64)
SELECT @handle = sql_handle from sys.sysprocesses where spid = @SPID
SELECT text FROM sys.dm_exec_sql_text(@handle)

The problem is if you run this in a stored proc for the current SPID all your going to get back is the statement above. I don't believe SQL server provides a T-SQL construct to execute a batch under a different SPID. I suppose you could write a .Net dll stored procedure that executes a batch on a different connection. to do that sort of thing but it may be more trouble than it's worth.