Using with As for recursive functions in SQL Server

Before sqlserver2005, it is cumbersome to implement recursion, for example, you might want to use a temporary table with a while statement to loop. Since sqlserver2005, the new with AS function syntax, the common expression (CTE), makes recursive implementations easier.

In this chapter we mainly demonstrate how to use the with as function to achieve a simple recursive function.
Let's look at the syntax of the CTE before this:

[With <common_table_expression > [,... n]]
<common_table_expression >::=
Expression_name [(column_name [,... n])]
As
(cte_query_definition)

parameter Description :
Expression_name:
A valid identifier for the common table expression. Expression_name must be different from the name of any other common table expression defined in the same with <common_table_expression > clause, but expression_name can be the same as the name of the base table or base view. Any reference to Expression_name in a query uses a common table expression instead of a base object.

COLUMN_NAME:
Specify the column name in the common table expression. Duplicate names are not allowed in a CTE definition. The specified column an array must match the number of columns in the cte_query_definition result set. The column Name list is optional only if you provide a different name for all the result columns in the query definition.

CTE_query_definition:
Specifies a SELECT statement whose result set fills the common table expression. Except that the CTE cannot define another CTE, the CTE_query_definition SELECT statement must meet the same requirements as when creating the view.
If more than one cte_query_definition is defined, these query definitions must be joined together with one of the following set operators: Union ALL, Union, EXCEPT, or INTERSECT.

--start Example Demo--

First, create a warehouse table with the table named Storage_depository, which has three fields: did (warehouse number), Dname (warehouse name), PID (parent warehouse number).
With such a simple table, you can create a tree structure from all the warehouse information through did and PID fields.
To create the SQL statement for the table:

Create Table Storage_depository
(
did varchar (#) NOT NULL primary key,
Dname varchar () NOT NULL,
PID varchar () null
)

Then insert the demo data into the table:

Insert into Storage_depository (did,dname,pid)
Select ' A ', ' a warehouse ', NULL
UNION ALL
Select ' A-1 ', ' A-1 warehouse ', ' A '
UNION ALL
Select ' A-2 ', ' A-2 warehouse ', ' A '
UNION ALL
Select ' A-1-1 ', ' a-1-1 warehouse ', ' A-1 '
UNION ALL
Select ' B ', ' B warehouse ', NULL

From the above data can be seen, a Zicang for A-1 and A-2 Warehouse, and A-1-1 for the A-1 of the sub-warehouse, B is a separate warehouse, and a warehouse lateral.
Below, we use the with AS function to detect all the sub-positions below the position a:

With W_storage_depository as
(
Select Did,dname,pid from storage_depository where did= ' A '
UNION ALL
Select A.did,a.dname,a.pid from Storage_depository a,w_storage_depository B where A.pid=b.did
)
SELECT * FROM W_storage_depository

The code is short and very easy to understand.

In turn, for example, we want to find out all the upper positions of the A-1-1 warehouse, slightly change the above SQL statement can be:

With W_storage_depository as
(
Select Did,dname,pid from storage_depository where did= ' a-1-1 '
UNION ALL
Select A.did,a.dname,a.pid from Storage_depository A,
W_storage_depository B where A.did=b.pid
)
SELECT * FROM W_storage_depository http://www.lmwlove.com/ac/ID748

Using with As for recursive functions in SQL Server