3 delete queries in one in sp? - 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 2005 Forums

Transact-SQL (2005)

3 delete queries in one in sp?

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 21:58:32

Hi.
I have a sp that will do delete in 3 steps.I was wondering if there is a better solution because if a step after the first one fails then (if i'm not mistaken) all data one the previous step would have been deleted and the other data will be left hanging.

Connections are: operations.animalid FK on animal.id
drugsoperations.operationsid fk on operations.id

The queries are as follows,without xtra if's etc.
I was thinking to do a while but it seemed more risky.Anyway.

Step 1

delete from drugsoperations where drugsoperations.operationsid
in(select id from operations where animalid in (select id from animals
where animals.flag =99))

Step2

delete from operations where operations.id in(SELECT operations.id  
FROM         drugsoperations RIGHT JOIN
                      operations ON drugsoperations.operationsid = operations.id
where drugsoperations.id is null)

Step 3

delete from animals where animals.id in(SELECT animals.id
FROM         Animals left JOIN
                      operations ON Animals.id = operations.animalid
where operations.id is null and animals.flag = 99)

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-17 : 22:07:46

using
begin tran
commit tran or rollback tran

That guarantees either ALL delete actions are committed or NONE

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 22:21:32

Hi.
Should i but it above the first query(begin tran T;)
and at the end of the last query(Commit tran t;)
There are many begin end and if's and returns inside the sp, will i need to change anything on them?
Thanks

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 22:35:19

Also i'm using a statement right after delete
set @vlaueX=@@rowcount

If after that i do :
IF @@ERROR <> 0
BEGIN
ROLLBACK .....

Will i get the @@error or i first must pass it to a variable?
Thanks.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 23:16:16

I solved it another way but i will be good to know.
Thanks.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-17 : 23:26:19

There are many begin end and if's and returns inside the sp, will i need to change anything on them?

May not

I prefer to use with try-catch. But I think you can do with @@ERROR too

ex:

begin tran
-- delete 1
-- delete 2
-- delete 3
commit tran

if @@ERROR <> 0
rollback tran

if you can just post the code here, it is easier for people to present a proper solution

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-17 : 23:39:51

Hi.
I'm doing the exact same thing except i use the rollback, first.Is this ok?

IF @@ERROR <>0
 BEGIN
  PRINT 'Error Occured'
Rollback tran t1;
return 99
 END

Commit tran t1;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 00:18:47

quote:
Originally posted by sapator

Hi.
I'm doing the exact same thing except i use the rollback, first.Is this ok?

IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
Rollback tran t1;
return 99
END

Commit tran t1;

It's fine. Most code I've seen does it in the reverse though.

I do this type of thing in a TRY/CATCH so that I can gracefully catch the errors.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-18 : 00:35:47

For try catch do you need to set your SQL so it supports .net statements?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-18 : 00:37:35

I've found a useful link.
I suppose is right on the T-SQL to use after all?

http://msdn.microsoft.com/en-us/library/ms175976.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-18 : 00:39:22

Yep. Microsoft added it to T-SQL starting with SQL Server 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-18 : 06:12:58

Thanks i'll have an extensive look !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources