RAISERROR - SQL Server Forums

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

Transact-SQL (2000)

RAISERROR

Author

Topic

natas
Yak Posting Veteran

51 Posts

Posted - 2005-04-08 : 20:10:09

RAISERROR('an error occured',17,1)

IF(@@ERROR=0)
BEGIN
UPDATE tabell SET somedude='test' WHERE ID=1
END

Hello..

Why does the execution continues with the UPDATE even though i have raised an error?

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-04-08 : 20:22:34

By itself, the RAISERROR statement does not stop execution of the procedure I think you need to have a RETURN for that

natas
Yak Posting Veteran

51 Posts

Posted - 2005-04-08 : 20:26:05

But why?

I have checked the @@ERROR varible for error number (IF(@@ERROR=0))and if there is an ERROR number the Execution should not have continued with the update, but it does so anyway. Somethings wrong here

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 20:37:48

The severity level determines if the code that generated the error is still running or not.

Check out the BOL topic: Error Message Severity Levels

Be One with the Optimizer
TG

natas
Yak Posting Veteran

51 Posts

Posted - 2005-04-08 : 20:45:27

It does not mather if i raise the severity level to 18. And if i raise it above 18 i get this error message:

Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 20:57:10

What happens when you run this code?


RAISERROR('an error occured',17,1)
IF(@@ERROR=0)
BEGIN
	PRINT 'UPDATE tabell SET somedude=''test'' WHERE ID=1'
END
Else
begin
	print 'Update did not occur'
End

Are you sure you don't have statements between you raiserror and your check of @@Error?

Be One with the Optimizer
TG

natas
Yak Posting Veteran

51 Posts

Posted - 2005-04-08 : 21:04:12

Are you sure you don't have statements between you raiserror and your check of @@Error?

No, i have a lot of other statement between?, but does it mather?. If an error occures, the @@ERROR variable shouldnt get resetted?, isnt it so?.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 21:12:26

The @@Error variable is reset after every statement including non transactional statements such as setting variable values.
In other words, @@error only checks the error value of the statement immediately preceeding the check of @@Error.

Be One with the Optimizer
TG

natas
Yak Posting Veteran

51 Posts

Posted - 2005-04-08 : 21:18:22

The @@Error variable is reset after every statement including non transactional statements such as setting variable values.

Aaaah. Thank you for the solution

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 21:19:19

you're welcome!

Same thing for @@Rowcount. If you want to capture the values of @@Error and @@Rowcount from a statement you have to perform the check in one statment. ie:

Select @err = @@Error, @rows = @@Rowcount

If you do this:
Select @rows = @@Rowcount
Select @err = @@Error

@@Error will always be 0 (unless an error occured reading @@rowcount) :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources