Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2008-01-28 : 13:18:49
|
| Is there a way to capture Stored Procedure Name along with the Parameter passed in SQL 2005 or SQL2000 when a stored procedure is executed?Thanks ! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-28 : 13:24:00
|
| Sure..fire up Profiler and run the application...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-01-28 : 13:30:34
|
| What I want is whenever a user runs a stored procedure with the parameter I would like to store that information in a table. Currently I am doing it this way and I am able to store the stored procedure name but not the parameters. Any help is appreciated.CREATE PROCEDURE [dbo].[sp_getTotals] ( @porttName VARCHAR(100), @asOf DATETIME)ASBEGIN EXEC dbo.sp_LogRequest 'sp_GetAccountTotals','' SELECT * FROM databasename.dbo.viewname WHERE AsOf=@asOf AND (PortGrpName = @portName OR @portName = '')END |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-28 : 13:33:17
|
| You can create a table and INSERT into it at the beginning of the proc so everytime its called you know the parameters its called with. Also, you should not be using "sp_" prefix for your procs. SQL Server thinks its system proc and looks under master db first and if it doesnt find there then it checks in the DB you are connected to. So there's this additional overhead.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-01-28 : 13:53:19
|
| Well, that's what I am doing in the beginning by calling the EXEC dbo.sp_LogRequest 'sp_GetAccountTotals','' but I am not sure I to capture the parameters passed. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-28 : 14:06:34
|
| EXEC dbo.sp_LogRequest @porttName will insert whatever the value of @porttName was when the proc was called.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-29 : 06:43:23
|
Also - looks like you are not logging the correct name for the sproc anyway.OBJECT_NAME(@@procid) is fool (and copy & paste) proof  |
|
|
|
|
|
|
Capture Stored Procedure along with Parameters