Casual Logging

Often when running a script, or a stored procedure (with many distinct sections) it’s handy to be able to have the classic “I’m here and the time is X” Logging. I often use this when first looking at monitoring and optimisation of processes.

I thought, for my first post , while I get things a little more organised behind the scenes. I’ll put something simple that I keep coming back to here, for my own notes as much as anything else.

Logging in an SQL Script

The below code spits out to the messages tab in SSMS (or other log locations when automating tasks), the date and time followed by a customisable log message.

PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' - ' + 'Log Message Here';

Logging in a stored Procedure

The below extends the code above for use in a stored procedure. It ads the functionality to output the name of the currently running procedure (using the @@PROCID) . This is a handy reference to use as it gets calculated dynamically, and if we copy/paste between procedures there is no need to remember to change the text containing the procedure name.

I have worked in environments where stored procedures are nested, and make up long running ETL processes. Sometimes this output simplifies seeing the flow of procedures in a complex implementation.

PRINT CONVERT(VARCHAR(20), GetDate(), 120) + ' : ' + OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + 'Log Message Here';