SQL stepless classification recursive deletion subclass

1 create procedure usp_catalogdeletechild
2 (
3 @ catalogid nvarchar (50)
4)
5as
6 set nocount on
7
8 declare @ childcatalogid nvarchar (50)
9 declare @ loop int
10 declare @ totalrow int
11
12 Delete from cms_catalog where [ID] = @ catalogid
13 begin
14 ---------- recursion, using temporary tables
15 select [ID], identity (INT) As I into # TMP from cms_catalog where parentid = @ catalogid
16 set @ totalrow = @ rowcount
17 set @ loop = 1
18
19 while (@ loop <= @ totalrow)
20 begin
21 select @ childcatalogid = [ID] From # TMP where I = @ Loop
22 set @ loop = @ loop + 1
23 exec usp_catalogdeletechild @ childcatalogid
24 end
25end
26go
27