Obtain the stored procedure of all sub-categories (CTE recursion) based on the parent level

A Stored Procedure for writing a project by yourself. The parent class ID is used to query all descendant classes under an unlimited category.

CTE is available only when sql2005 or above, so the database version must be above 2005.

Use [fetioncore] Go/****** object: storedprocedure [DBO]. [parentidcte] script Date: 09:44:08 ******/set ansi_nulls ongoset quoted_identifier ongo -- ================ =================================-- Author: abaal -- create Date: 20111214 -- Description: obtain all subcategories based on the parent level (CTE recursion) -- ===================================================== ====== create procedure [DBO]. [parentidcte] (@ parentid INT) asbegin set nocount on; SET transaction isolation level read uncommitted; with temp (ID, name, parentid, level) as (select [p]. [ID], [p]. [name], [p]. [parentid], 0 as level from [DBO]. [portraittype] as P where [p]. [parentid] = @ parentid -- ID of the node to be queried -- the preceding query is initialized, therefore, you only need to query the top layer of the node Union all -- get the ID of the subnode select [p]. [ID], [p]. [name], [p]. [parentid], level + 1 from [DBO]. [portraittype] P join temp as C on [p]. [parentid] = C. id -- the preceding query is used for recursion. It joins the last result of the CTE to obtain the sublayer node of the last result.) Select ID, name, parentid, level from tempend set ansi_nulls onset quoted_identifier ongoexec sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' obtain all sub-classes based on the parent level (CTE recursion) ', @ level0type = n' schema ', @ level0name = n 'dbo', @ level1type = n' procedure ', @ level1name = n' parentidcte'