SQL Server Returns recursive result sets.

There is a recursion in the work that needs to be used in many places. I don't want to define every stored procedure, but the view cannot define variables, so I have to write them as functions.

Create Function [DBO]. [f_getdwdata]
(
@ ID int
)
Returns @ t_level table (ID int, level INT) -- recursively queries all subordinate unit data based on the passed ID.
Begin
Declare @ level int
Set @ level = 0
Insert @ t_level select ID, @ level
From mm_dw
Where id = @ ID
While @ rowcount> 0
Begin
Set @ level = @ LEVEL + 1
Insert @ t_level select a. ID, @ level
From mm_dw A, @ t_level B
Where a. SJID = B. ID
And B. Level = @ level-1
End
Return
End

The call method is simple:

Select * From f_getdwdata (@ ID)

In SQL Server 2000, manual loops are used for recursion.

 

In SQL Server 2005, you can use the following method:

Create Function [DBO]. [f_getdwdata]
(
@ ID int
)
Returns @ t_level table (ID int, level INT) -- recursively queries all subordinate unit data based on the passed ID.

Begin

With Act (ID, SJID)
As
(
Select ID, SJID from rzeam. mm_dw
Where id = @ ID
Union all
Select a. ID, A. SJID
From rzeam. mm_dw A, Act
Where a. SJID = act. ID
)

Insert @ t_level
Select ID, SJID from Act

Return
End