Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Quick Hints for using the RAISERROR Command Expand / Collapse
Author
Message
Posted Wednesday, November 23, 2005 2:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 28, 2013 1:27 PM
Points: 116, Visits: 15

Noel,

Thanks.  I have lots of practice!

Post #239293
Posted Wednesday, November 23, 2005 3:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, Visits: 74
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
Posted Wednesday, November 23, 2005 3:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, Visits: 74
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
Posted Wednesday, November 23, 2005 3:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, November 23, 2005 3:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, Visits: 74
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
Posted Thursday, November 24, 2005 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, November 24, 2005 2:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, Visits: 74
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
Posted Thursday, November 24, 2005 3:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 2:55 AM
Points: 118, Visits: 57
Thanks very much
Post #239550
Posted Monday, November 28, 2005 5:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:18 AM
Points: 419, Visits: 559

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
Posted Tuesday, May 23, 2006 3:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase ««123»»

Permissions Expand / Collapse