How to grab input parameters from a stored procedure (SQL Server 2005)?
I would like to create a shared store for my stored procedures, allowing me to log the values of the input parameters. I am currently doing this more or less manually and I am very unhappy with this approach. Ideally, I would like to say something like this:
"given my spid, what are my props and their values?"
This is the same information that was provided to me when I ran SQL Profiler - the stored procedure name, all input parameters and all input values are listed for me. How can I get these values from a stored procedure?
Thanks; Duncan
a source to share
This would be difficult to do in a stored procedure. The SQL Profiler runs under a different SPID and runs a statement like this to capture other user 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 you are going to return is the statement above. I do not believe that SQL Server provides a T-SQL construct to execute a package under a different SPID. I suppose you could write a dll.Net stored procedure that executes a package on a different connection. doing this kind of thing, but it could be more of a problem than it's worth.
a source to share