Stored Procedure Help

[CK]

I have a one to many relationship between a billing table and a task table.
I want to write a procedure I can pass a billingID to and it will go delete
all the task records with that billingID as well as the as the single
billing record. Like referential integrity.

There are two other task tables taskcharge and taskfunding. When I delete a
task from the task table I want to delete the related records from those two
tables as well.

Bottom line, I want to delete a billing record then delete then child
records in the task table, as they are deleted I need to delete the
taskcharge and taskfunding records child records of the task. The task table
has the billingID field and a taskID field. The taskcharge and taskfunding
tables have the taskID field only.

Can this be done? I suggested trying triggers but this customer doesn't want
to use triggers in delete operations. How do I accomplish this.

Any advice is appreciated.

Thanks in Advance,
CK

[Doug Lawry]

Look up "Cascading Referential Integrity Constraints" in Transact-SQL Help. This
does it for you!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"CK" <c_**********@hotmail.com> wrote in message
news:n9*******************@newssvr13.news.prodigy. com...

I have a one to many relationship between a billing table and a task table.
I want to write a procedure I can pass a billingID to and it will go delete
all the task records with that billingID as well as the as the single billing
record. Like referential integrity.

There are two other task tables taskcharge and taskfunding. When I delete a
task from the task table I want to delete the related records from those two
tables as well.

Bottom line, I want to delete a billing record then delete then child records
in the task table, as they are deleted I need to delete the taskcharge and
taskfunding records child records of the task. The task table has the
billingID field and a taskID field. The taskcharge and taskfunding tables
have the taskID field only.

Can this be done? I suggested trying triggers but this customer doesn't want
to use triggers in delete operations. How do I accomplish this.

Any advice is appreciated.

Thanks in Advance,
CK

[CK]

There were no constraints defined on the tables. Can I do it this way?

ALTER PROC spDeleteBillingContract (@BillingContractID INT)
AS
DELETE FROM TaskOrderCharge WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrderFunding WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrder WHERE BillingContractID=@BillingContractID
DELETE FROM BillingContract WHERE BillingContractID=@BillingContractID

RETURN
"Doug Lawry" <la***@nildram.co.uk> wrote in message
news:dv**********@nntp0.reith.bbc.co.uk...

Look up "Cascading Referential Integrity Constraints" in Transact-SQL
Help. This does it for you!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"CK" <c_**********@hotmail.com> wrote in message
news:n9*******************@newssvr13.news.prodigy. com...

I have a one to many relationship between a billing table and a task
table.
I want to write a procedure I can pass a billingID to and it will go
delete all the task records with that billingID as well as the as the
single billing record. Like referential integrity.

There are two other task tables taskcharge and taskfunding. When I
delete a task from the task table I want to delete the related records
from those two tables as well.

Bottom line, I want to delete a billing record then delete then child
records in the task table, as they are deleted I need to delete the
taskcharge and taskfunding records child records of the task. The task
table has the billingID field and a taskID field. The taskcharge and
taskfunding tables have the taskID field only.

Can this be done? I suggested trying triggers but this customer doesn't
want to use triggers in delete operations. How do I accomplish this.

Any advice is appreciated.

Thanks in Advance,
CK

[DickChristoph]

Hi CK

That looks like it should work, I would suggest adding "Begin Transaction"
at the start of the SP and "Commit Transaction" at the end. That way you can
be sure all of the rows in the four tables are either deleted or not (should
one step fail for some reason)

Additionally if TaskOrder is a very large table you might consider putting
the TaskOrderIDs to be deleted in a Temp table rather than searching the
TaskOrder Table three times, particularily if TaskOrder.BillingContractID is
not indexed.

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:jP*****************@newssvr27.news.prodigy.ne t...

There were no constraints defined on the tables. Can I do it this way?

ALTER PROC spDeleteBillingContract (@BillingContractID INT)
AS
DELETE FROM TaskOrderCharge WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrderFunding WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrder WHERE BillingContractID=@BillingContractID
DELETE FROM BillingContract WHERE BillingContractID=@BillingContractID

RETURN
"Doug Lawry" <la***@nildram.co.uk> wrote in message
news:dv**********@nntp0.reith.bbc.co.uk...

Look up "Cascading Referential Integrity Constraints" in Transact-SQL
Help. This does it for you!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"CK" <c_**********@hotmail.com> wrote in message
news:n9*******************@newssvr13.news.prodigy. com...

I have a one to many relationship between a billing table and a task
table.
I want to write a procedure I can pass a billingID to and it will go
delete all the task records with that billingID as well as the as the
single billing record. Like referential integrity.

There are two other task tables taskcharge and taskfunding. When I
delete a task from the task table I want to delete the related records
from those two tables as well.

Bottom line, I want to delete a billing record then delete then child
records in the task table, as they are deleted I need to delete the
taskcharge and taskfunding records child records of the task. The task
table has the billingID field and a taskID field. The taskcharge and
taskfunding tables have the taskID field only.

Can this be done? I suggested trying triggers but this customer doesn't
want to use triggers in delete operations. How do I accomplish this.

Any advice is appreciated.

Thanks in Advance,
CK

[CK]

That's much cleaner. Thanks for the tips. Works great!!!

CK

"DickChristoph" <dc********@yahoo.com> wrote in message
news:C0******************@tornado.rdc-kc.rr.com...

Hi CK

That looks like it should work, I would suggest adding "Begin Transaction"
at the start of the SP and "Commit Transaction" at the end. That way you
can be sure all of the rows in the four tables are either deleted or not
(should one step fail for some reason)

Additionally if TaskOrder is a very large table you might consider putting
the TaskOrderIDs to be deleted in a Temp table rather than searching the
TaskOrder Table three times, particularily if TaskOrder.BillingContractID
is not indexed.

--
-Dick Christoph
"CK" <c_**********@hotmail.com> wrote in message
news:jP*****************@newssvr27.news.prodigy.ne t...

There were no constraints defined on the tables. Can I do it this way?

ALTER PROC spDeleteBillingContract (@BillingContractID INT)
AS
DELETE FROM TaskOrderCharge WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrderFunding WHERE TaskOrderID IN
(SELECT TaskOrderID From TaskOrder WHERE
BillingContractID=@BillingContractID)
DELETE FROM TaskOrder WHERE BillingContractID=@BillingContractID
DELETE FROM BillingContract WHERE BillingContractID=@BillingContractID

RETURN
"Doug Lawry" <la***@nildram.co.uk> wrote in message
news:dv**********@nntp0.reith.bbc.co.uk...

Look up "Cascading Referential Integrity Constraints" in Transact-SQL
Help. This does it for you!
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"CK" <c_**********@hotmail.com> wrote in message
news:n9*******************@newssvr13.news.prodigy. com...
I have a one to many relationship between a billing table and a task
table.
I want to write a procedure I can pass a billingID to and it will go
delete all the task records with that billingID as well as the as the
single billing record. Like referential integrity.

There are two other task tables taskcharge and taskfunding. When I
delete a task from the task table I want to delete the related records
from those two tables as well.

Bottom line, I want to delete a billing record then delete then child
records in the task table, as they are deleted I need to delete the
taskcharge and taskfunding records child records of the task. The task
table has the billingID field and a taskID field. The taskcharge and
taskfunding tables have the taskID field only.

Can this be done? I suggested trying triggers but this customer doesn't
want to use triggers in delete operations. How do I accomplish this.

Any advice is appreciated.

Thanks in Advance,
CK

[--CELKO--]

>> There were no constraints defined on the tables.<<

A really bad design flaw that you can correct. Do so.

Also, quit putting "sp_" prefixes on procedure names and follow
ISO-11179 conventions and have "<verb><object>" style names.