error problem - 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

error problem

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2009-04-24 : 08:02:36

[code]IF NOT EXISTS(SELECT * FROM Employee_Master WHERE empid=@empid OR lname=@lname AND fname=@fname AND mname=@mname)
BEGIN
INSERT INTO Employee_Master
(empid,lname,fname,mname,address,contactno,marital,tinid,sssid,philid)
VALUES(@empid,@lname,@fname,@mname,@address,@contactno,@marital,@tinid,@sssid,@philid)

INSERT INTO Employee_Detail
(empid,dept_pos,rate,rate_type,shiftcode,dhired,stat)
VALUES(@empid,@dept_pos,@rate,@rate_type,@shiftcode,@dhired,@stat)

SET @result = 'Successfully Save'
END
ELSE
BEGIN
RAISERROR 14000 'Existing Record'
ROLLBACK TRANSACTION
END[/code]

i have this problem if one of the table has an error it will continue to execute to next procedure of line? how can i stop it to continue execution?...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 08:16:34

[code]IF EXISTS (SELECT * FROM Employee_Master WHERE empid = @empid OR lname = @lname AND fname = @fname AND mname = @mname)
BEGIN
RAISERROR 14000 'Existing Record'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT Employee_Master
(empid,lname,fname,mname,address,contactno,marital,tinid,sssid,philid)
VALUES (@empid,@lname,@fname,@mname,@address,@contactno,@marital,@tinid,@sssid,@philid)

IF @@ERROR <> 0
BEGIN
RAISERROR 14001 'Master Insert Failed'
ROLLBACK TRANSACTION
END

INSERT Employee_Detail
(empid,dept_pos,rate,rate_type,shiftcode,dhired,stat)
VALUES (@empid,@dept_pos,@rate,@rate_type,@shiftcode,@dhired,@stat)

IF @@ERROR <> 0
BEGIN
RAISERROR 14002 'Detail Insert Failed'
ROLLBACK TRANSACTION
END

SET @result = 'Successfully Save'
END[/code]

E 12°55'05.63"
N 56°04'39.26"

wormz666
Posting Yak Master

110 Posts

Posted - 2009-04-24 : 12:09:22

I cant understand, why you dont have 'Into in the Insert Statement', and it will be possible, that if the second table failed the first one will cancel first table transaction?....

finding for thru
making a maze on my mind

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources