generating log timestamps using Stored procedures in SQL server 2005

Question

I have a Stored Procedure, Which has some select Statements and insert statements.

Is there any way , I can log the Timestamps of execution before and after the sqls inside the Stored procedure?

Answer 1

Sure. Add TSQL to write to an audit table at the start and end of execution, adding TRY/CATCH error handling to make sure an early exit doesn't occur.

Answer 2

If this is not something you want to leave in permanently (i.e. it's just for debugging/performance analysis purposes) then your best bet is to use SQL Profiler and monitor the SP:StmtCompleted event which will record the stats for each statement within a sproc. You can dump this data to a db table.

Edit: Running SQL Profiler:
1) In SSMS, under Tools, select SQL Server Profiler
2) Connect to your db server you want to monitor
3) In the trace properties dialog that appears, go to the Events Selection tab and tick the "Show all events" checkbox
4) The grid will then show all types of events you can monitor. Find the Stored Procedures section, and in there cick the SP:StmtCompleted checkbox to define that you want to monitor that type of event.
5) The general tab allows you to save the trace to a file, or to a db table if you want to. Or, you don't have to save it to either, just display it to screen. You can always save it to a table/file later if you really need to.
6) When you're ready just click "Run"
For a lot more info on SQL Profiler, see MSDN

If this is something you want to keep (i.e. an audit table), then you'll need to INSERT records into your own audit table yourself e.g.

DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()

SELECT Something FROM SomeTable WHERE....

INSERT MyAuditTable (Statement, StartTime, EndTime)
VALUES ('SELECT Something FROM SomeTable WHERE...', @StartTime, GETDATE())

Answer 3

You may want to have a look at this one here.

Taking advantage of Table Variables NOT being transactional - application logging files within a transaction and keeping what's happened