Error handling in several Querys

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 in several Querys

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-07-23 : 04:35:15

hi
i have app which has several childs to parent... example customer has several childs and also has parents
over a period of time the customer is inactive and i thought off moving it to a history database... i hope i am doing right or any other process better....?

10 master
90 childs

i have process like
1.insert master of customer in history database
2.insert customer
3.insert childs of customer
4.delete childs of customer
5.delete customer

currently i am doing it through vb using ado

i am planning to do it by storeprocedure
this means i have several insert and delete statments...

the problem is if i use @@errors to check any errors occured
my code will have more than statment checks...

and i have transaction throught vb i am able to do but how will i do it in store procedure..

thanks

======================================
Ask to your self before u ask someone

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-23 : 08:01:50

It is advisable to run this type of job with a stored procedure.

Look for help in BOL on:
BEGIN TRAN/COMMIT TRAN
ROLLBACK TRAN/SAVEPOINT

Do you need to delete the records or flag them as deleted?
Can you restore any record, as needed? Are you using IDENTITY numbers for primary/foreign keys?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-07-23 : 23:42:41

thanks YellowBug...

i am using transacion that not the problem... how do i find out if there was any problem.

no i dont mark flag record i delete to reduce the base

no i dont have IDENTITY numbers for primary/foreign keys i have my own number some thing like this AXXXX

i need method through which i can checkfor errors and rollback my transaction.... other than
select @@errors....

thanks

======================================
Ask to your self before u ask someone

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-24 : 04:20:06

quote:

i need method through which i can checkfor errors and rollback my transaction.... other than
select @@errors....

Why can you not use @@error ?
Maybe you could clarify the problem as you see it

HTH
Jasper Smith

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-24 : 05:08:29

begin tran
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit

successexit:
commit tran
return
errexit:
rollback tran
raiserror('failed',16,-1)
return @error

You can also set a char field on error and include it with the raiserror.

You can also include

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-07-25 : 04:31:07

quote:

begin tran
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit
insert ...
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
goto errexit

successexit:
commit tran
return
errexit:
rollback tran
raiserror('failed',16,-1)
return @error

thanks a lot nr that was gr8 but if u see the code what most we see
"select @error = @@error, @rowcount = @@rowcount ........"

as i early posted i have huge list of table so several insert delete statment....

i was looking for something like ON ERROR GOTO ERRO in visual basic
that way my code will be easy... so be simple i want to avoid the same lines....

thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-07-30 : 04:06:18

so what the point do we have any other way ....

======================================
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-30 : 07:03:36

What exactly is the problem? You asked how to handle errors in SQL Server stored procedures and nr provided you with code that does just that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources