Problem with error handling query?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Problem with error handling query??

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-09 : 09:13:37

Hi..
I have error handling query..like
-------------------
create table emp (ID int primary key,Name varchar(4))
-------------------

begin tran

Insert into emp values (1,'SS')
Insert into emp values (2,'DA')
Insert into emp values (3,'BA')
Insert into emp values (1,'SD')

IF (@@error <> 0) GOTO ErrorHandler

commit tran

ErrorHandler:

If (@@error <> 0)
Begin
declare @MsgErr as varchar(8000)
select @MsgErr = description from dbo.sysmessages where error = @@error
Print 'Duplicate'
Rollback tran
end

....But what is happening here I am getting error message of duplicate..but all other records got processed..What I want here is as if there is any duplicate records found all the transaction should get rolled back..and also i am not getting print 'Duplicate' statement
can anybody have any idea what is wrong with query??

T.I.A

mallier
Starting Member

24 Posts

Posted - 2006-02-09 : 09:25:30

--U have to check error after every insert statement
-- seee below code

begin tran
declare @err int
declare @MsgErr as varchar(8000)

Insert into emp values (1,'SS')
select @err=@@error
IF (@err <> 0) GOTO ErrorHandler
Insert into emp values (2,'DA')
select @err=@@error
IF (@err <> 0) GOTO ErrorHandler
Insert into emp values (3,'BA')
select @err=@@error
IF (@err <> 0) GOTO ErrorHandler
Insert into emp values (1,'SD')
select @err=@@error
IF (@err <> 0) GOTO ErrorHandler

goto EndSave
ErrorHandler:
if (@@trancount>0) rollback
select @MsgErr = description from dbo.sysmessages where error = @@error
Print 'Duplicate'

EndSave:
if (@@trancount>0) commit tran

cheers,
http://mallier.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 09:30:19

you need to refer this
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 09:30:42

From Books Online

quote:
@@ERROR
Returns the error number for the last Transact-SQL statement
executed.

Which means
- If (@@error <> 0) will be 0
- where error = @@error will also be 0

----------------------------------
'KH'
everything that has a beginning has an end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 09:34:08

quote:
select @MsgErr = description from dbo.sysmessages where error = @@error

Should be master..sysmessages (unless you are running your query in master database). Also change the @@error to @err as mallier suggested

----------------------------------
'KH'
everything that has a beginning has an end

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-09 : 10:32:01

thanx ppl :) :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources