Quick Hints for using the RAISERROR Command

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Quick Hints for using the RAISERROR Command

22 posts, Page 2 of 3

««1 2 3 »»

Quick Hints for using the RAISERROR Command

Rate Topic

Display Mode

Topic Options

Author

Message

Richard Moldwin

Posted Wednesday, November 23, 2005 2:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 09, 2012 7:54 AM
Points: 116, Visits: 13

Noel,

Thanks. I have lots of practice!

Post #239293

BeerBeerBeer

Posted Wednesday, November 23, 2005 3:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:17 PM
Points: 239, Visits: 68

I like the concept but I generally take a slightly different approach.

Use of a logging table that is populated by a single stored procedure that takes in the descriptions and dates as supplied. You can use a control table to determine when to turn the debugging on or off and if you need to change the output message format, you can just change the one stored procedure. The stored procedure can also do the dirty work for you converting values. Write code once and your stored procs don't start to get too messy.

EXEC DebugSP @message, Paramlist

EXEC DebugSP @message, Paramlist

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #239309

BeerBeerBeer

Posted Wednesday, November 23, 2005 3:14 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:17 PM
Points: 239, Visits: 68

Oops, slight error in my example - lets try again

EXEC DebugSP @message, Paramlist

-- Statement block runs here

EXEC DebugSP @message, Paramlist

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #239310

Noel Kennedy

Posted Wednesday, November 23, 2005 3:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 2:55 AM
Points: 118, Visits: 57

Except of course this won't work if your code rolls back a transaction because your message records will be rolled back aswell

Post #239317

BeerBeerBeer

Posted Wednesday, November 23, 2005 3:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:17 PM
Points: 239, Visits: 68

Yes, good point.

Take the two pronged approach and use a RAISERROR inside the stored proc too. This way you get messages as they happen but also an easily searchable set if things don't go wrong.

And again, if you have the RAISERROR inside it's own stored proc, you only have to write it once.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #239319

Noel Kennedy

Posted Thursday, November 24, 2005 3:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 2:55 AM
Points: 118, Visits: 57

Have you got a script you could post for your 'DebugSP' proc? I am doing something now where this would be extremely useful? =)

Post #239406

BeerBeerBeer

Posted Thursday, November 24, 2005 2:17 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:17 PM
Points: 239, Visits: 68

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.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #239546

Noel Kennedy

Posted Thursday, November 24, 2005 3:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 2:55 AM
Points: 118, Visits: 57

Thanks very much

Post #239550

mosaic-263591

Posted Monday, November 28, 2005 5:07 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:37 AM
Points: 419, Visits: 556

Thanks for a useful and clear article - much appreciated.

One comment: as an initial step the SQL profiler can also be useful - no coding required! Of course you cannot see how the parameters flow through the procedure, but the various statements it executes may give an indication of the program flow.

Post #239902

Patrick-304111

Posted Tuesday, May 23, 2006 3:36 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, July 27, 2012 2:55 PM
Points: 22, Visits: 79

excellect opps

Post #282225

« Prev Topic | Next Topic »

22 posts, Page 2 of 3

««1 2 3 »»

Permissions