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

Posted by Duncan on Stack Overflow See other posts from Stack Overflow or by Duncan
Published on 2010-05-17T21:55:06Z Indexed on 2010/05/17 22:00 UTC
Read the original article Hit count: 412

Filed under:

log

|

stored

|

procedure

|

input

|

parameters

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

© Stack Overflow or respective owner

Related posts about log

Logrotate not doing any rotation

as seen on Super User - Search for 'Super User'
I just set up LogRotate on my RHEL6 server so that it rotates my custom Apache log files. However, it doesn't do anything when i try manually running it. I expect it to rotate the log files "access.log" and "err.log". They have been there for a few days and need to be rotated. Here is the output: [root@pc1… >>> More
ORA-03113 in code. In addition, TNS-12535 and ORA-03137 in alert file

as seen on Stack Overflow - Search for 'Stack Overflow'
I've got an exception that contain ORA-03113: (SiPPSS.GetPrintWorkDirectDetail) - ERR:ORA-03113: end-of-file on communication channel Process ID: 7448 Session ID: 30 Serial number: 9802 ?????:12110937 ????:T855 Oracle.DataAccess.Client.OracleException ORA-03113: end-of-file on communication channel… >>> More
PostgreSQL 8.4 won't start after blackout

as seen on Server Fault - Search for 'Server Fault'
I have problem with starting PostgreSQL 8.4 on Ubuntu 9.10 Server after blackout. When I try to connect to the database it says: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. When I try to start it by… >>> More
repeated failing passwords in linux security log (/var/log/secure)

as seen on Server Fault - Search for 'Server Fault'
Recently, I opened up the SSH port through my firewalls (and redirecting to my server) so I could check on the (http) server while on the road. The first week or two there was nothing different. But now, three or four weeks later, I see lots of this: Mar 20 08:38:28 localhost sshd[21895]: pam_unix(sshd:auth):… >>> More
How can I back up my ubuntu system?

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I'm sure there's a lot of questions on here similar to this, and I've been reading them, but I still feel this warrants a new question. I want nightly, incremental backups (full disk images would waste a lot of space - unless compressed somehow.) Preferably rotating or deleting old backups when running… >>> More

Related posts about stored

Retreive a value inside a stored procedure and use it inside that stored procedure

as seen on Stack Overflow - Search for 'Stack Overflow'
delimiter // CREATE DEFINER=root@localhost PROCEDUREgetData(IN templateName VARCHAR(45),IN templateVersion VARCHAR(45),IN userId VARCHAR(45)) BEGIN set @version = CONCAT("SELECT saveOEMsData_answersVersion FROMsaveOEMsData WHERE saveOEMsData_templateName = '",templateName,"' ANDsaveOEMsData_templateVersion… >>> More
use result set of mysql stored procedure in another stored procedure

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a MYSQL stored procedure SP1() that returns a result set. I want to call SP1() inside of SP2() and loop through the result set of SP1() to do some additional work. I don't want to include my logic from SP1() because it would make SP2() too complicated. Any suggestions? Thanks. >>> More
Creating Stored Procedures in MySQL Using HeidiSQL 4&#146;s Stored Routine Editor

as seen on Internet.com - Search for 'Internet.com'
In the "Write MySQL Queries Using HeidiSQL 4" article, we learned how to connect to a MySQL database and execute queries against it using the free HeidiSQL GUI client. Today, we'll learn how to create a stored procedure using HeidiSQL's Stored Procedure Editor. >>> More
"Parameter type conflict" when calling Java Stored Procedure within another Java Stored Procedure

as seen on Stack Overflow - Search for 'Stack Overflow'
Here's the problem (sorry for the bad english): i'm working with JDeveloper and Oracle10g, and i have a Java Stored Procedure that is calling another JSP like the code: int sd = 0; try { CallableStatement clstAddRel = conn.prepareCall(" {call FC_RJS_INCLUIR_RELACAO_PRODCAT(?,?)} "); clstAddRel… >>> More
Calling stored procedure from another stored procedure and returning result as new columns

as seen on Stack Overflow - Search for 'Stack Overflow'
How to call stored procedure from another stored procedure and return the result as first one's column? ALTER PROCEDURE [dbo].[GetItems] AS SET NOCOUNT ON SELECT ID, AddedDate, Title,Description, Result of Stored Procedure "CountAll" call with parameter ID as Total FROM dbo.Table1 And… >>> More