November 25, 2010 at 12:11 pm
Can any one help me in modifying the stored proc to log usage to a central table....
Thanks in advance
Sid
November 25, 2010 at 12:17 pm
Either at the beginning or end of the proc add an insert statement.
Insert the name of the proc, datetime that it was used, and the spid that called it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2010 at 12:19 pm
Some people will also create a new proc to perform the insert I just described. Then instead of an insert, you exec the proc with the required parameters (beginning or end of the proc but preferably at the beginning).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2010 at 12:39 pm
Thanks Jason,
i understand the first solution but how would i include it in the stored proc for example lets take below stored proc as the example
Case When isnumeric(PreOrderNum)=1
Then PreOrderNum
Else Null
End as PreOrderNum
From Sid.dbo.Orders with(nolock)
Where (OrderNum=@OrderNum)
Go
November 25, 2010 at 2:28 pm
prasadau2006 (11/25/2010)
Thanks Jason,i understand the first solution but how would i include it in the stored proc for example lets take below stored proc as the example
Case When isnumeric(PreOrderNum)=1
Then PreOrderNum
Else Null
End as PreOrderNum
From Sid.dbo.Orders with(nolock)
Where (OrderNum=@OrderNum)
Go
The provided example is not a stored proc but merely a snippet of code that would belong inside a stored proc.
In this case, after you add a select to that snippet, you would put in the insert statement between the proc declaration and the select statement that you would add to that snippet.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply