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.