A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.<\/p>\n
A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries,<\/b> for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.<\/p>\n
A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps.<\/p>\n
\u00a0<\/b><\/p>\n
<\/p>\n
–create table #Employee(vmenuid\u00a0 varchar(25) not null,vmenuname\u00a0 varchar(25)\u00a0 null,vparent\u00a0 varchar(25) null);<\/p>\n
–insert into #Employee values(‘M001′\u00a0\u00a0\u00a0\u00a0 ,’HR System’,\u00a0\u00a0\u00a0\u00a0 Null),(‘M002’ ,\u00a0\u00a0 ‘Payroll’,\u00a0\u00a0\u00a0\u00a0\u00a0 ‘M001’),<\/p>\n
–(‘M003’,\u00a0 ‘Salary Benefits’,\u00a0\u00a0\u00a0\u00a0\u00a0 ‘M002’),<\/p>\n
–(‘M004′\u00a0\u00a0 ,’Recruitment’,\u00a0\u00a0 ‘M001’)<\/p>\n
select * from dbo.#Employee;<\/p>\n
WITH security_menu_Recursive(Parent,MenuId,MenuName,LEVEL)<\/p>\n
AS<\/p>\n
(<\/p>\n
SELECT vparent,vmenuid,vmenuname,0 AS LEVEL FROM dbo.#Employee WHERE vParent is NULL<\/p>\n
UNION ALL<\/p>\n
SELECT vparent,vmenuid,vmenuname,Level + 1 AS LEVEL FROM dbo.#Employee<\/p>\n
INNER JOIN security_menu_Recursive AS smr ON smr.menuid = dbo.#Employee.vParent<\/p>\n
)<\/p>\n
SELECT parent,menuid,menuname,LEVEL FROM security_menu_Recursive<\/p>\n
<\/p>\n
–best query for CTE and declare table example in following link\u2014<\/p>\n
http:\/\/stackoverflow.com\/questions\/12827236\/tsql-recursive-cte-inefficient-need-an-alternative<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained. A query is referred to as a recursive query when it references a recursive CTE.… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,14],"tags":[],"class_list":["post-318","post","type-post","status-publish","format-standard","hentry","category-c-vb","category-sql-server","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=318"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/318\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=318"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}