DML delete Stored Procedure - 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

SQL Server 2000 Forums

Transact-SQL (2000)

DML delete Stored Procedure

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-05-04 : 10:46:49

Guys,

I have bunch of dml sql statements that I run in particular order like

delete from employee where id < 100
delete from department where empid < 100
delete from accounts where empid < 100

I want above dml statements to run as a part of stored procedure where I pass id/empid as an argument.

execute datatrunc (100)

is it possible to this, any suggestions/inputs regarding how to go about doing it would help.

Thanks

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 10:55:50

Sure: [CODE]Create spMyDelete (@empid int, @id int) as
set nocount on
begin
delete from employee where id < @id
delete from department where empid < @empid
delete from accounts where empid < @empid
end
set nocount off[/CODE]If it is important to you that all of them either happen together or all don't happen you can wrap it with BEGIN TRANSACTION and check for error and do a ROLLBACK or COMMIT as appropriate.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 10:55:55

Create Procedure Delete_tables
(@id int, @empid int)
as
delete from employee where id < @id
delete from department where empid < @empid
delete from accounts where empid < @empid

After you create that sp, run

execute datatrunc 100

Madhivanan

Failing to plan is Planning to fail

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-05-04 : 11:33:56

druer, madhivanan

Thanks for the response Am I correct to assume that the delete statements will run in the same order that
they were written in procedure

Create spMyDelete (@empid int, @id int) as
set nocount on
begin
delete from accounts where empid < 100
delete from department where empid < 100
delete from employee where id < 100
end
set count off

In my schema accounts and department (child) have foreign key relationship with employee table (parent), hence
I would deletes on accounts and department table before runing the delete on employee table.

So my question is the stored procedure run the delete statements in the same order that they written in it, I just
want to make sure that the stored procedure doesnt create a process for each delete ststement and run them in
parallel. Since it is imperative that we delete the child tables before parent table.

any thoughts

Thanks

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-04 : 11:49:22

It will run in order. But I recommend wrapping them in a transaction like druer suggested.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-04 : 13:09:50

The stored proc will delete them in the order that they occur in the stored procedure. So be sure to put them in the order that they will need to be to avoid the FK constraints from erroring out.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-05 : 04:31:10

If there is referential integrity between tables, delete child tables before deleting parent tables

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources