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

(

@dtStart DATETIME

, @dtEnd DATETIME

-- Variable list here

)

AS

DECLARE @ReturnCode INT

BEGIN

-- Set the default return value to success

SELECT @ReturnCode = 0

IF EXISTS ( SELECT

1

FROM

debug_control

WHERE

dbg_can_debug = 1

)

BEGIN

-- 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

SELECT

@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.

, 1

, @sStringVar

, @lInteger

, @fFloat

, @byUnsignedInt

) WITH LOG

, NOWAIT

INSERT dbo.debug_log

(

-- Add apropriate columns here

)

SELECT

-- Select approapriate data here

FROM

-- Use from if you want to peek at data being processed

-- Check for any errors

SELECT @ReturnCode = @@ERROR

END

-- Return any errors found along the way

RETURN @ReturnCode

END

GO

Hope you find this useful, sorry I don't have more time to flesh it out for you.