How can I log stored procedure call?

Question

How can I log stored procedure call - time, title, input params, output params?

Answer 1

If you're using the enterprise version of SQL2K8 you should look into SQL Server Audit.

http://msdn.microsoft.com/en-us/library/dd392015(v=SQL.100).aspx

A more do-it-yourself approach is to create an audit table and a stored procedure to insert into it, and at beginning of each proc that you want to log, call the stored procedure and pass in the parameter values concatenated into a single string.

Edit:

Here's an interesting blog post I found as well. It provides some automation to add the logging functionality to stored procedures. I've not looked at it or tested it, but it's probably worth a look.

http://blogs.msdn.com/b/jenss/archive/2009/06/08/light-weight-sql-server-procedure-auditing-without-using-sql-server-auditing.aspx

And an updated version from the same guy:

http://blogs.msdn.com/b/jenss/archive/2010/07/10/light-weight-sql-server-procedure-auditing-without-using-sql-server-auditing-version-2.aspx