Hi, I want to have my sp to delete

[wuwang]

Hi,
I want to have my sp to delete child records.
The problem is the child record could be over 1.
I'm not allowed to create a table to put the child
records and delete them. Any idea to resolve this problem?
Thank you .....



--Here is the sp

declare @count int,
@inv_id int,
@child_inv_id int

set @inv_id = 1234

select @count = count(*) from table1 where parent_inv_id = @inv_id

if @count =0
begin
delete table1
where inv_id = @inv_id
end
else
begin
select @child_inv_id = inv_id
from table1
where parent_inv_id = @inv_id
--delete the child record
delete table1
where inv_id = @child_inv_id
--delete the parent record
delete table1
where inv_id = @inv_id
end

 

[DLanouette]

Is there a reason that you count the child records?

If you just did the delete, instead of a count, it should delete ALL teh child records that match, not just one or two. See below.

Am I missing something?



DECLARE @inv_id INT

SET @inv_id = 1234

--delete the child record
DELETE table1
WHERE parent_inv_id = @inv_id

--delete the parent record
DELETE table1
WHERE inv_id = @inv_id




______________________________
- David Lanouette
- Lanouette Consulting, LLC
- DLanouette@Computer.org