SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / Quick Hints for using the RAISERROR Command / Latest Posts

RE: Quick Hints for using the RAISERROR Command

Jeff Moden — Wed, 15 Feb 2012 12:51:08 GMT

[quote][b]Richard Moldwin (11/23/2005)[/b][hr] T-SQL programming involves a major loss of productivity, compared to any other modern language I know of.[/quote]I know it's a wicked old post but the comment above is just so very wrong. It's like anything else... you have to know how to use the tool correctly in order to be productive with it.

RE: Quick Hints for using the RAISERROR Command

Josep — Thu, 23 Oct 2008 09:31:01 GMT

Is it possible to log the RAISERROR messages into a table and not just to the Windows Even Log? I've some stored procedures with the following line: RAISERROR(@Message,10,1) WITH NOWAITand I'd like to have all this in a table without the need to modify it. So, the solution told by Steve about using a stored procedure like lsp_Debug couldn't be used.Maybe in the way to execute it?

RE: Quick Hints for using the RAISERROR Command

Patrick-304111 — Tue, 23 May 2006 15:36:00 GMT

excellect opps

RE: Quick Hints for using the RAISERROR Command

mosaic-263591 — Mon, 28 Nov 2005 05:07:00 GMT

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.

RE: Quick Hints for using the RAISERROR Command

Noel Kennedy — Thu, 24 Nov 2005 15:02:00 GMT

Thanks very much

RE: Quick Hints for using the RAISERROR Command

BeerBeerBeer — Thu, 24 Nov 2005 14:17:00 GMT

Here is a conceptual look at oneAll 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 )ASDECLARE @ReturnCode INTBEGIN -- 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 @ReturnCodeENDGOHope you find this useful, sorry I don't have more time to flesh it out for you.

RE: Quick Hints for using the RAISERROR Command

Noel Kennedy — Thu, 24 Nov 2005 03:11:00 GMT

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

RE: Quick Hints for using the RAISERROR Command

BeerBeerBeer — Wed, 23 Nov 2005 15:51:00 GMT

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.

RE: Quick Hints for using the RAISERROR Command

Noel Kennedy — Wed, 23 Nov 2005 15:45:00 GMT

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

RE: Quick Hints for using the RAISERROR Command

BeerBeerBeer — Wed, 23 Nov 2005 15:14:00 GMT

Oops, slight error in my example - lets try againEXEC DebugSP @message, Paramlist -- Statement block runs hereEXEC DebugSP @message, Paramlist

RE: Quick Hints for using the RAISERROR Command

BeerBeerBeer — Wed, 23 Nov 2005 15:12:00 GMT

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, ParamlistEXEC DebugSP @message, Paramlist

RE: Quick Hints for using the RAISERROR Command

Richard Moldwin — Wed, 23 Nov 2005 14:06:00 GMT

Noel,

Thanks. I have lots of practice!

RE: Quick Hints for using the RAISERROR Command

noeld — Wed, 23 Nov 2005 13:45:00 GMT

Richard: Nice rant

RE: Quick Hints for using the RAISERROR Command

Richard Moldwin — Wed, 23 Nov 2005 12:55:00 GMT

Nice article. It's just too bad that there is a need for it.

Error handling in SQL Server 2000 is utterly brain-dead. Whenever I can, I prefer to code in DMO/ADO just to avoid this "little" issue, and the equally brain-dead SQL debugger. Unfortunately, the DMO work-around is often inadequate or too slow, forcing my hand to use kludges like this. T-SQL programming involves a major loss of productivity, compared to any other modern language I know of.

RE: Quick Hints for using the RAISERROR Command

noeld — Wed, 23 Nov 2005 12:10:00 GMT

That is not entirely true. If the severity level used in the RAISERROR WITH LOG is between 0 and 18 you don't need to be part of the sysadmin role but if the value is between 19 and 25 you must be sysadmin

Good article by the way

RE: Quick Hints for using the RAISERROR Command

John Scarborough — Wed, 23 Nov 2005 10:24:00 GMT

Very nice article. Short, simple, useful. A few typos help me think about what I'm doing, so I won't complain about those!

RE: Quick Hints for using the RAISERROR Command

David.Poole — Wed, 23 Nov 2005 10:12:00 GMT

Thanks, Old SQL 6.5 habits die hard. Although I know it exists I rarely use the object_name function.Does anyone know the correct syntax for getting decimal places to appear in the RAISERROR statements? As I said in the article BOL says you can do it but I simply couldn't get it to work.

RE: Quick Hints for using the RAISERROR Command

Aaron Dutton — Wed, 23 Nov 2005 09:41:00 GMT

Thanks for the enlightening article about RAISEERROR. I just wanted to point out a couple things.

I think there's a typo in the very last example. The line is:

SET @sMessage = CONVERT(CHAR(19),@dtEnd,120) + ' <proc name>:<block description> started'

But it should be:

SET @sMessage = CONVERT(CHAR(19),@dtStart,120) + ' <proc name>:<block description> started'

Also, I think you can get the current stored procedure name using:

 object_name(@@PROCID)

So you wouldn't have to hardcode it in your literal.

Cheers!

RE: Quick Hints for using the RAISERROR Command

Chris Roesener — Wed, 23 Nov 2005 07:36:00 GMT

Nice article. To Sean's point -- you could wrap the calls with IF IS_SRVROLEMEMBER('sysadmin') = 1 to prevent problems with the user not having sysadmin.

RE: Quick Hints for using the RAISERROR Command

mccool — Wed, 23 Nov 2005 07:18:00 GMT

Lots of extra commas were showing up in the first example for some reason. It should read:

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

RE: Quick Hints for using the RAISERROR Command

Sean Fackrell — Wed, 23 Nov 2005 03:45:00 GMT

I think if you use RAISERROR WITH LOG in your production stored procedures, users executing the procedure that aren't members of the sysadmin role will cause the procedure to fail so this is a limitation of its use in this scenario. You can get round this by using a debug or trace bit parameter and only calling RAISERROR if this parameter is set to 1. This may not always be possible in a production environment unless you are able to change the client calling code to switch the trace on/off.

Quick Hints for using the RAISERROR Command

David.Poole — Wed, 12 Oct 2005 16:39:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/quickhintsforusingtheraiserrorcommand.asp