question on @@error !!! - 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

General SQL Server Forums

New to SQL Server Programming

question on @@error !!!

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-01-20 : 06:06:23

i have a query that returns error because the table does not exist

lets say

begin tran
insert into not_exist_table (x) values ('1')
if @@error <>0
begin
print 'this doesn't print'
rollback -- doesnot either rollback
end

the statement returns the error (table not exist) without checking the @@error and then later when i check the select @@error it prints the error no.
any help please?

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 06:15:16

begin tran
insert into mytable (mycol) values ('1')
GO
if @@error <>0
begin
print 'this doesn''t print'
rollback -- doesnot either rollback
END
else
begin
print 'this prints'
commit
end

Rahul Shinde

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-20 : 06:22:19

quote:
Originally posted by ra.shinde

begin tran
insert into mytable (mycol) values ('1')
GO
if @@error <>0
begin
print 'this doesn''t print'
rollback -- doesnot either rollback
END
else
begin
print 'this prints'

commit
end

Rahul Shinde

This will again give the same error message..

Karthik

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 06:25:08

ya, but now the message get printed and transaction is also rolled back

Rahul Shinde

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-20 : 06:26:46

quote:
Originally posted by ann06

i have a query that returns error because the table does not exist

lets say

begin tran
insert into not_exist_table (x) values ('1')
if @@error <>0
begin
print 'this doesn't print'
rollback -- doesnot either rollback
end

the statement returns the error (table not exist) without checking the @@error and then later when i check the select @@error it prints the error no.
any help please?

try this if it is sqlserver 2000

begin tran
if exists( select 1 from sysobjects where type ='u' and name = 'not_exist_table')
begin
insert into not_exist_table (x) values ('1')
commit tran
end
else
begin
print 'this doesn''t print'
rollback -- doesnot either rollback
end

If u are using sqlserver 2005 then make use of TRY - CATCH Blocks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources