If Exists and @@Error

Question

If Exists and @@Error

ron k

SSCertifiable

Points: 6103
More actions
April 25, 2005 at 9:27 am

#68979

I'm a little puzzled as where to trap for @@Error when using "IF Exists". For example:
If Exists (Select * from mytable)
    Begin
    <True Branch> ...
    End
Else
    Begin
    <False Branch> ...
    End
<Continuation of Code>...
Where is the best place to trap @@Error to determine if the Select was successful? I understand that "fatal" errors will abort the proc and I have that covered by the way I process return codes. What about a less than "fatal" error?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

One does not typically try to capture @@error for the select statement contained within the IF EXISTS.

In the case of an assignment select, result set select, or a create table select, one can and perhaps should get/look at @@ERROR.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

Ron, why would you need to trap @@error with your statement?

Why not just handle success or failure with a flag e.g.

Declare a_flag

If Exists (Select * from mytable)

Begin

<True Branch> ...

SET a_flag = 0

End

Else

Begin

<False Branch> ...

SET a_flag = 1

End

Then handle your results based around the flag setting.

Just a thought.......

Have fun

Steve

We need men who can dream of things that never were.

ron k SSCertifiable Points: 6103 More actions · Answer 3

Steve,

Thanks for the suggestion. I'm also consdering:

Declare @pk int,

@error int

Select top 1 @pk = MyPrimaryKey

From MyTable

Where ...

Select @error = @@error

If @error <> 0

Begin

Raiserror ....

Return ...

end

If @pk is not null

Else

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Hi Ron,

If you are anticipating 0, 1 or many results from your query and you are trying to trap 0 (which is what I am interpreting here) @@ERROR is not the way forward.

@@ERROR will only change if the code actually generated an error - for example the table name was incorrect and the table did not exist in the database.

0, 1 or many situations should be handled in your own code to move to your choice of branch.

Check out @@ERROR in BOL.

Have fun

Steve

We need men who can dream of things that never were.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

I think Ron is actually looking for errors (and not numbers of rows in result). However I see no reason to do so. For instance, if the table has been removed he will still not catch an error in @@error, since the severity level of 16 makes the procedure stop executing.

So Ron, unless you have some specific reason which I cannot see I think you should simply go ahead and do IF EXISTS ( SELECT * FROM .... ) without any error checking for that. Then do your branches.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

ron k SSCertifiable Points: 6103 More actions · Answer 6

Chris,

Yes, my concern is trapping for errors. I would like to make the code as bulletproof (within reason) as possible.

I'm not so concerned about a "fatal" error since as you mentioned that would stop the proc and I have return code procedures that will catch that event.

It's some other "non-fatal" error that concerns me. In such a case the proc will continue to process and the results could be incorrect. I'm not sure what specific events could cause this to happen but in Sql Server there always seems to something lurking. So until we have Try/Catch I'm trying to be extra careful with code for a medical application.

Thanks for your input. - ron