Quick Hints for using the RAISERROR Command
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/quickhintsforusingtheraiserrorcommand.asp
-
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.
-
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
-
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.
-
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!
-
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.
-
Very nice article. Short, simple, useful. A few typos help me think about what I'm doing, so I won't complain about those!
John Scarborough
MCDBA, MCSA -
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
* Noel -
Nice article. It's just too bad that there is a need for it.
<Rant On>
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.
<Rant off>
-
Richard: Nice rant
* Noel -
Noel,
Thanks. I have lots of practice!
-
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
-
Except of course this won't work if your code rolls back a transaction because your message records will be rolled back aswell
-
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.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply