Here is a conceptual look at one
All you need to do is fill in the bits. I've borrowed some of the stuff Dave talked about in his article and just layed out the concept for you.
Apply your own standards and away you go.
CREATE PROCEDURE lsp_Debug
, @dtEnd DATETIME
-- Variable list here
DECLARE @ReturnCode INT
-- Set the default return value to success
SELECT @ReturnCode = 0
IF EXISTS ( SELECT
dbg_can_debug = 1
-- Calculate time elapsed if you have an end date as well as a start date
-- Send message to event log so even if a rollback occurs we can see how far we got
DECLARE @sStringVar VARCHAR(440)
, @lInteger INT
, @fFloat DECIMAL(6,2)
, @byUnsignedInt TINYINT
@sStringVar = 'A string'
, @lInteger = 44
, @fFloat = 3.14
, @byUnsignedInt = 56
RAISERROR('The string variable contains %s, the int contains %d, the float contains %d, the uint contains %d'
, 10 -- Informational severity only.
) WITH LOG
-- Add apropriate columns here
-- Select approapriate data here
-- Use from if you want to peek at data being processed
-- Check for any errors
SELECT @ReturnCode = @@ERROR
-- Return any errors found along the way
Hope you find this useful, sorry I don't have more time to flesh it out for you.
Life without beer is no life at all
All beer is good, some beers are just better than others