"Raising Error" versus Godzilla

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"Raising Error" versus Godzilla

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-28 : 12:11:39

Seriously: "Raising an Error versus Return Value"

I've been writing all my stored procedures to return an error value rather than raising an error.

Anyone know of an obvious situation where raising an error is a better method than a return value?

Sam

sorengi
Starting Member

48 Posts

Posted - 2004-05-28 : 15:44:30

Return Codes for error handling provide less detailed information -- unless you sit down and define them for your application in advance.

Raising an error can use system predefined errors, or custom errors, and can provide more detail to an exception. Also, if your application is catching the errors raised by SQL Server, it will automatically catch all raised errors, so less coding could be involved on the client side.

This is my 2 cents...
Michael D.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-28 : 16:13:03

I use RaisError to return step information in along sproc I am debugging. That way it is easy to see which steps were executed and if you include timestamps in the message you can also tell who long each portion of the sproc took.

my penny...
Corey

Kristen
Test

22859 Posts

Posted - 2004-05-29 : 00:41:07

Sam,

I use a unique return value for each point in the SProc where an error could occur; they are documented at the top of the SProc, so if "MySProc" returns 3 I can just check the header comment to see what that means.

I also have a LOG table which SProcs INSERT into; that has 4 varchar columns for whatever information the SProc wants to store (obviously the columns hold different things for different SProcs, but its only the techies that need to interpret it). At exit my SProcs INSERT a row if the return value is non-zero - I can post some sample code if you want a specific example, I'd be happy for it to be picked to pieces!

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-29 : 07:52:40

Kristen,

Yep. I code return values the same way you do. The error log may be a good idea for some procs.

Sam

Kristen
Test

22859 Posts

Posted - 2004-05-30 : 05:10:28

I couldn't survive if only some of SProcs were doing this

We have @intDebug as a final parameter on everything (default to 0 [OFF]) so I can lift something from our traces or Profiler and squirt it through QA with the extra "@intDebug=1" parameter for "verbose mode" - makes it very easy to get a good diagnosis real-quick, but I have NO IDEA what all that redundant IF @intDebug >=1 BEGIN ... END code is doing to overal performance (but, as yet, we have no issue with performance so I've not started to worry about that yet).

I guess I should come clean that we have WAY more logging than this, and performance is still OK.

On entry every [except the lowest level 3 or 4 SProcs] logs that it was called, and what parameters it was passed (a concatenated string). On exit it UPDATEs that log row to store the elapsed time, the RETURN VALUE (i.e. whether OK [0] or ERROR [Non-0]) and up to 4 "general purpose" values that might be useful - e.g. number of rows returned.

This allows me to:

Review logs for any error-returning SProcs

Review SProcs taking longer than "normal" (including finiding ones that are "getting slower")

Finding SProcs that did not return at all (the LOG has a START entry which was NOT updated on RETURN). This leads us to Run-Time Syntax errors, but has also in the past lead us to ["known", as it turned out] SQL sever bugs.

Session information. Every SProcs takes the "session ID" as the first parameter; this is part of the log, and therefore I can see a list of every Web Page a user visited, together with every SProcs that page called, and the nested SProcs that they called. Together with the parameters passed to each Sproc.

I have a couple of optimisation tools bench tested for when this becaomes "too expensive"

1. Change to only record EXIT from SProc - this does not tell me that an SProc started but did not finish, but the "caller" should establish that (checking @@ERROR after EXEC MyNestedSProc and so on).

2. Change to only log the initial SProc calls from the Web application (which should allow me to run QA to discover what the nested SProcs were etc.)

but until performance is an issue the overwelming volume of available diagnostic info is a huge help in moving forwards as quickly as possible.

I've got a new-hire whos been here a month. He comes from an ASP-with-embedded-dynamic-SQL background and he cannot believe the number of SProc calls we are doing minute-by-minute relative to what his old companys SELECT * approach

used to achieve

But please all speak up if you think I am nuts! I came here to learn ... but I try to help a little when I think I am able, which of course leads me to discover that sometimes I only *thought* I was able

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-30 : 08:57:01

Sounds comprehensive. Probably a better way to go than what I've done so far.

I've been returning the proc exec time to the ASP so the page footer shows the total rendering time, and the time for key procs that are called (like the footer on sqlteam shows page render time). It helps identify when things start to slow down and when a fix is working.

Does anyone else use a table or tables to hold proc execution time / error information?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-30 : 09:46:00

>> Does anyone else use a table or tables to hold proc execution time / error information?
Depends on the app. I will put trace nfo into the data access layer in the app to write to a file when the trace flag is on
http://www.nigelrivett.net/VB6DataAccessLayer.html
That will give the time off all the db accesses and the parameters (you can get that from the profiler though).

I will sometimes use a similar method to write to a table from SPs - but that's usually used for debugging in more detail and usually temporary.
I don't like using the return value. If I get an error I'll raise an error. If it's something that needs to bbe conveyed to the app then an output parameter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-05-30 : 16:27:51

I like raising an error when I'm working with .NET

Since raising an error with a error level of 16 will throw a SQLException that I can catch in my code.

Dustin Michaels

Kristen
Test

22859 Posts

Posted - 2004-05-31 : 02:42:13

I must be being thick here. How do you catch a RAISERROR generated in a nested SProc call?

I was assuming checking @@ERROR after the EXEC would tell me ... but it seems to be ZERO by then

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-31 : 09:45:37

A simple test will show that it does set @@error and you can pick it up after the select. Note that it is reset after on each statement so it must be saved directly after the exec. The seeverity must be greater than 10 to set @@error and up to 18 to be vailable to non-sysadmins. I always use 16.
create proc a
as
select 'a'
raiserror ('failed a',16,-1)
return
go

create proc b
as
declare @error int
select 'b'
exec a
select @error = @@error
if @error <> 0
raiserror ('failed b from a err = %d',16,-1, @error)
return
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-05-31 : 10:58:26

OK, thanks for that. I found my error, I had something like:


raiserror ('failed a',16,-1)
return 123

in which case @@ERROR is NOT available to the caller :-(

So plenty of room for human error then to have some statement between RAISERROR and RETURN, or have RETURN accidentally return a value. Blast! I was hoping for something to reduce possibility for human error.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-31 : 18:49:40

Sam,

I'm at home and some one at a loss..

Go to my blog and pick up the tql template for error handling....

It doesn't address xp_cmdshell error handling...Nigel gave me that one...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-31 : 20:04:49

There's a man who has priorities

just back from vacation, check the action on SQLTeam.

Gotta love this place.

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-05-31 : 20:54:16

Personally, I almost never write any custom errors.. The only exception (pun intended) is when I am doing transactions... Just outside (ie around) the DAL sits a exeception handling class that uses a XML document (basically a customsied sysmessages table XML'ised) to translate into readable messages...

DavidM

"Always pre-heat the oven"

Kristen
Test

22859 Posts

Posted - 2004-06-01 : 01:53:11

quote:
Originally posted by SamC

I've been writing all my stored procedures to return an error value rather than raising an error.

Anyone know of an obvious situation where raising an error is a better method than a return value?

Damn it Sam, your thread caused me to make some experiments.

I've been doing the Return Value thingie too ...

However, it seems to me that the RAISERROR route might score because a nested subroutine call has gotta check for @@ERROR anyway - to cover Run Time errors in the child SProc. So maybe it would be better if that was ALL it had to check ...

But the RAISERROR thing seems immensely fragile to me - it must be followed by an immediate RETURN which has NO parameter

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-01 : 09:04:31

quote:
But the RAISERROR thing seems immensely fragile to me - it must be followed by an immediate RETURN which has NO parameter

Not being a RAISERROR kind of guy, I would have bet money that you're wrong about that, and I still might take the bet since I haven't seen your experimental results, and you could be mistaken.

@@Error values must be saved next step, but RAISERROR - you're saying the error is cleared if some SQL statement is executed other than return? Say it isn't so...

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-01 : 13:25:51

Sam....

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-01 : 14:03:44

>> Say it isn't so...
Not me.
Simple to test - see the script I gave above.

Usually clients capture all the raiserrors - it's t-sql that doesn't have access to the error collection.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-01 : 14:14:24

quote:
Usually clients capture all the raiserrors - it's t-sql that doesn't have access to the error collection.

That helps and thanks.

RAISEERROR messages and values are returned all the way to the .NET client for proper rendering... BUT isn't available to T-SQL beyond the next executed statement (that can change the condition).

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-01 : 14:30:21

Nigel,

Ran your code...saw the @Error value...

But then why?


USE Northwind
GO

CREATE PROC mySproc99 AS SELECT 1 raiserror ('failed a',16,-1) Return -1 
GO

DECLARE @rc int, @Error int

EXEC @rc=mySproc99

SELECT @Error = @@ERROR

SELECT @rc, @Error
GO

DROP PROC mySproc99
GO

Does it supercede it?

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources