Error handling inside SPROC 1

[link9]

Hello all --

Hopefully a simple one for you.

I have several SPROCs that create dynamic SQL statements inside themselves and then execute those statements through the use of:

sp_executesql

Here's the thing, though. They use division in their execution, and from time to time, the denominator in the equation turns out to be a zero. Currently, I keep the number of operations to 1 inside the SPROC and then if it returns any error whatsoever, I assume it was a divide by zero, and then return an appropriate answer to my user in the ASP page where it's used.

However, I would like to handle these errors inside the SPROC so that I could do multiple operations with one call to the SPROC and then have the SPROC return the appropriate values depending on whether or not a divide by zero was encountered on any of the three operations I wish to complete.

So far, I know I can check for an error like this:

IF @@error != 0
/*something went wrong -- presumably a divide by zero, so do something

That's all fine and dandy, but I've been unable to figure out how to clear that error inside the SPROC so that I can go on and do another operation, and then use the same methodology to find out if an error occured there.

In ASP, it's err.clear -- to reset the error value to 0, but how would I do this in the SPROC?

Or, if you have any words of wisdom on how I might improve this train of thought, that would also be welcome.

Thanks for your input.
Paul Prewett

 

[Tim1]

Hi Paul,

If the SQL statement is generated dynamically, could you not just test whatever could make the dominator zero, and if that is so, skip the sp_executesql command, instead just marking a flag which your app can read to report a divide by zero error.

I hope this makes sense, but the more I tried to explain, the more I got confused!!

Tim

 

[link9]

Yes, I understand, but it would be less efficient, I believe to first run a check and then go into an IF-ELSE --

whereas on the other hand, you just build the statement, execute it, and move on -- checking your errors as you go.

95% of the time, there isn't an error, so while I give you that it would be more efficient the 5% of the time that the base would be 0 (since I would never have to build the SQL statement up in the first place) -- I think building it run most efficiently most of the time is the way to go here....

So can you tell me how to clear the value of @@error inside a SPROC?

Thanks!
Paul Prewett

 

[RickCole]

You could re-set @@error back to zero by running some thing like SET NOCOUNT ON.

i.e.

CREATE PROCEDURE upz_test
AS

select @@error
-- Error is now set to 0


RAISERROR('Setting Error value', 16, 1)
select @@error
-- Something runs which causes @@Error value not to be 0


SET NOCOUNT ON
select @@error
-- Something now runs which causes @@Error value back to 0



Rick.

 

[RajD]

Hi,

Try using a CASE satement with the denominator to check if it is 0. If yes then replace it with 1.

Instead of using z= x/y
use z= x/(case when y = 0 then 1 else y end)

RajD

 

[link9]

RajD, I think your solution still adds the inefficiency of first checking to see if the result is 0.

RickCole, here's what I found in BOL about NOCOUNT:
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

I don't see how this could reset the value of @@error -- am I missing something here, or does it also have that side-effect?

Thanks for the input!
Paul Prewett

 

[RickCole]

I was using SET NOCOUNT ON just as an example (and not a very good one) but here is what I was aiming for:

SET NOCOUNT ON will ways work when issued as a TSQL command therefore they will successful set @@error to 0.

However you could easily change SET NOCOUNT ON in my previous example to SELECT GETDATE() or any other SQL command which you know will be successful as this again will set @@error to 0.

Hope this make a bit more sense.

As an aside it's good practice to always have SET NOCOUNT ON at the beginning of each stored procedure. Normally your not interested in any number of rows returned and having this option set does make the store procedure more efficient.

Rick.

 

[link9]

Ahhhhh... **light bulb flicks on**

Perform a statement you KNOW won't return an error, and thereby set the @@error back to 0. Good idea.

Thank you.
Paul Prewett