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 2005 Forums

Transact-SQL (2005)

Delete Stored Procedure

Author

Topic

burnseymufc
Starting Member

4 Posts

Posted - 2008-10-03 : 06:43:05

I am trying to write a stored procedure that will delete from a top level downwards. Everything is in one table called Modules. I can add a Module to the table and a sub module to the table. A sub module can be linked to a module and a sub module can be linked to a sub module and so on. These are how they are linked:

ID Name Parent

9 Histopathology Null --Module
40 Cervical 9 --Sub Module
41 Cancer 9 --Sub Module
900 Liver 40 --Sub Module
901 Virtual Slides 40 --Sub Module
904 Cytopathology 41 --Sub Module
1006 Neurology 41 --Sub Module

Each sub module's is related to its parent by the 'parent' and 'id'.

How can I go about deleting all of these using a stored procedure if I only have the id of the top module with an id of 9?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 07:19:12

Use a CTE

declare @test table
(
ID int,
Name varchar(100),
Parent int
)

INSERT INTO @test
SELECT 9, 'Histopathology' ,Null  UNION ALL--Module
SELECT 40, 'Cervical', 9  UNION ALL --Sub Module
SELECT 41, 'Cancer', 9   UNION ALL--Sub Module
SELECT 900, 'Liver', 40  UNION ALL --Sub Module
SELECT 901, 'Virtual Slides', 40  UNION ALL --Sub Module
SELECT 904, 'Cytopathology', 41  UNION ALL --Sub Module
SELECT 1006, 'Neurology', 41  UNION ALL
SELECT 11, 'pathology' ,Null UNION ALL
SELECT 200, 'Micropathology' ,11

DECLARE @ID int
SET @ID=9
;With CTE (ID, Name, Parent)AS
(

SELECT ID, Name, Parent
FROM @test
WHERE ID=@ID
UNION ALL
SELECT t.ID,t.Name,t.Parent
FROM @test t
INNER JOIN CTE c
ON c.ID=t.Parent
)
delete t from @test t
join CTE c
on c.ID=t.ID

select * from @test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources