@@error handling - 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

SQL Server Development (2000)

@@error handling

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-04 : 08:13:48

Mike writes "Why does the code below not execute the ELSE statement if there is an error generated in the SELECT statement....ie referencing a non existent table...

USE pubs
GO

DECLARE @ins_error int
BEGIN TRAN

SELECT * FROM xauthors
SELECT @ins_error = @@error

IF @ins_error = 0
BEGIN
COMMIT TRAN
INSERT INTO perrors VALUES ('xauthors select - success' , @ins_error)
END
ELSE
BEGIN
ROLLBACK TRAN
INSERT INTO perrors VALUES ('xauthors select - fail' , @ins_error)
END
GO"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-04 : 08:50:18

because an invalid object reference halts execution of that batch.

try this though . . .


USE pubs 
GO 

create procedure usp_xauthors as select * from xauthors
go

DECLARE @ins_error int 
BEGIN TRAN 

exec usp_xauthors
SELECT @ins_error = @@error 

IF @ins_error = 0 
BEGIN 
COMMIT TRAN 
INSERT INTO perrors VALUES ('xauthors select - success' , @ins_error) 
END 
ELSE 
BEGIN 
ROLLBACK TRAN 
INSERT INTO perrors VALUES ('xauthors select - fail' , @ins_error) 
END 
GO

<O>

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-04 : 19:13:15

quote:

Mike writes "Why does the code below not execute the ELSE statement if there is an error generated in the SELECT statement....ie referencing a non existent table...

...because you can't do error-trapping VB style in SQL

If you're worried about a non-existant table - why not use...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
rather than....
IF @ins_error = 0

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 06/04/2002 19:15:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources