SQL _ obtain subtopics Based on the parent column ID

-- A recursive function used to find the directory ID and then use this function to find the content
----------------------------------------------------------------

-- Drop function [DBO]. [getchildelement]
Create Function [DBO]. [getchildelement]
(
@ Parentid int
)
Returns
@ Tmptable table
(
Id int,
Fatherid int
)
As
Begin
Insert into @ tmptable
SELECT clsId, ParentId FROM T_nav_cls WHERE clsId = @ ParentId

DECLARE @ cnt int
SELECT @ cnt = count (clsId) FROM T_nav_cls WHERE ParentId = @ ParentId

IF @ cnt> 0
BEGIN
DECLARE @ itID int

DECLARE item_cursor CURSOR
SELECT clsId FROM T_nav_cls WHERE ParentId = @ ParentId

OPEN item_cursor
Fetch next from item_cursor
INTO @ itID

WHILE @ FETCH_STATUS = 0
BEGIN
Insert into @ TMPTable
SELECT * FROM GetChildElement (@ itID)
Fetch next from item_cursor
INTO @ itID
END
CLOSE item_cursor
DEALLOCATE item_cursor
END

RETURN
END

 

-- Select * from GetChildElement (1)

========================================================== ====================================
CURSOR usage example
-- Declare the cursor. admin is the name of a table, which identifies the attribute name of the table.

DECLARE Column_Name cursor for select name FROM sys. columns WHERE object_id = object_id ('admin ');

-- Open the cursor
OPEN Column_Name;
DECLARE @ Column_Name VARCHAR (100)

-- Get data from the cursor,
Fetch column_name into @ column_name

-- While loop: traverses the cursor. When @ fetch_status is 0, the cursor traversal is complete.
While (@ fetch_status = 0)
Begin

-- Print records
Print (@ column_name)

-- Obtain the next record
Fetch next from column_name into @ column_name;
End

-- Close the cursor
Close column_name

-- Delete a cursor
Deallocate column_name
-------------------------------------------------------------------------------
@ Fetch_status
Returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.

Return Value description
0. The fetch statement is successful.
-1 The fetch statement fails or this row is not in the result set.
-2 The extracted row does not exist.

Syntax
@ Fetch_status

Return type
Integer

Note
Because @ FETCH_STATUS is global for all the cursors on a connection, be careful to use @ FETCH_STATUS. After executing a FETCH statement, you must test @ FETCH_STATUS before executing another FETCH Statement on another cursor. The @ FETCH_STATUS value is not defined when any extraction operation comes forward.

For example, a user executes a FETCH statement from a cursor and then calls a stored procedure, which opens and processes the results of another cursor. After controlling the returned results from the called stored procedure, @ FETCH_STATUS reflects the final FETCH statement executed in the stored procedure, instead of the results of the FETCH statement before the stored procedure is called.

Example
The following example uses @ FETCH_STATUS to control the cursor activity in a WHILE loop.

DECLARE Employee_Cursor CURSOR
SELECT LastName, FirstName FROM Northwind. dbo. Employees
OPEN Employee_Cursor
Fetch next from Employee_Cursor
WHILE @ FETCH_STATUS = 0
BEGIN
Fetch next from Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor