How to call a recursive function in sql server

Question

My table is a bit green

cat_id  Cat_Name    Main_Cat_Id

1       veg         null
2       main course 1
3       starter     1
4       Indian      2
5       mexican     2
6       tahi        3
7       chinese     3
8       nonveg      null
9       main course 8
10      indian      9
11      starter     8
12      tahi        11
13      chinese     11

( Main_Cat_Id cat_id of the previous added category in which it belongs)

This table is used for categories in which the veg category has two subcategories, the main course and the starter , which is identified by main_cat_id and this subcategory again has a subcategory of both Indian and Mexican

And this categorization depends on the user, he can add an additional subcategory to the Indian, Mexican, so that he can have any level of categorization

node, veg,

(1)veg > (2)main course(1) > (4)indian(2)
                           > (5)mexican(2)
       > (3)starter(1)     > (6)thai(3)
                           > (7)chinese(3)

1,2,4,5,3,6,7

, sql

CREATE FUNCTION [dbo].[GetSubCategory_TEST]
( @MainCategory int, @Category varchar(max))
RETURNS varchar(max)
AS
BEGIN
    IF EXISTS (SELECT Cat_Id FROM Category WHERE Main_Cat_Id=@MainCategory)
    BEGIN
        DECLARE @TEMP TABLE
        (
            CAT_ID INT
        )
        INSERT INTO @TEMP(CAT_ID) SELECT Cat_Id FROM Category WHERE Main_Cat_Id=@MainCategory
        DECLARE @TEMP_CAT_ID INT
        DECLARE CUR_CAT_ID CURSOR FOR SELECT CAT_ID FROM @TEMP
            OPEN CUR_CAT_ID
            WHILE 1 =1
                BEGIN
                FETCH NEXT FROM CUR_CAT_ID
                INTO  @TEMP_CAT_ID;
                IF @@FETCH_STATUS <> 0
                    SET @Category=@Category+','+ CONVERT(VARCHAR(50), @TEMP_CAT_ID)
                    SET @Category = [dbo].[GetSubCategory](@TEMP_CAT_ID,@Category)
                END
                CLOSE CUR_CAT_ID
                DEALLOCATE CUR_CAT_ID
    END
    return @Category
END 

. , PLZ,

Answer 1

, CTE.

-

DECLARE @TABLE TABLE(
    cat_id INT,
    Cat_Name VARCHAR(50),
    Main_Cat_Id INT
)

INSERT INTO @TABLE SELECT 1,'veg',null
INSERT INTO @TABLE SELECT 2,'main course',1
INSERT INTO @TABLE SELECT 3,'starter',1
INSERT INTO @TABLE SELECT 4,'Indian',2
INSERT INTO @TABLE SELECT 5,'mexican',2
INSERT INTO @TABLE SELECT 6,'tahi',3
INSERT INTO @TABLE SELECT 7,'chinese',3
INSERT INTO @TABLE SELECT 8,'nonveg',null
INSERT INTO @TABLE SELECT 9,'main course',8
INSERT INTO @TABLE SELECT 10,'indian',9
INSERT INTO @TABLE SELECT 11,'starter',8
INSERT INTO @TABLE SELECT 12,'tahi',11
INSERT INTO @TABLE SELECT 13,'chinese',11

;WITH Recursives AS (
        SELECT  *,
                CAST(cat_id AS VARCHAR(MAX)) + '\' ID_Path
        FROM    @TABLE
        WHERE   Main_Cat_Id IS NULL
        UNION ALL
        SELECT  t.*,
        r.ID_Path + CAST(t.cat_id AS VARCHAR(MAX)) + '\'
        FROM    @TABLE t INNER JOIN
                Recursives r    ON  t.Main_Cat_Id = r.cat_id
)
SELECT  *
FROM    Recursives

Answer 2

, @astander scipt .

, . - , , XML, ( STUFF )

DECLARE @TABLE TABLE(
    cat_id INT,
    Cat_Name VARCHAR(50),
    Main_Cat_Id INT
)

DECLARE @Collected TABLE(
    cat_id INT
)

INSERT INTO @TABLE SELECT 1,'veg',null
INSERT INTO @TABLE SELECT 2,'main course',1
INSERT INTO @TABLE SELECT 3,'starter',1
INSERT INTO @TABLE SELECT 4,'Indian',2
INSERT INTO @TABLE SELECT 5,'mexican',2
INSERT INTO @TABLE SELECT 6,'tahi',3
INSERT INTO @TABLE SELECT 7,'chinese',3
INSERT INTO @TABLE SELECT 8,'nonveg',null
INSERT INTO @TABLE SELECT 9,'main course',8
INSERT INTO @TABLE SELECT 10,'indian',9
INSERT INTO @TABLE SELECT 11,'starter',8
INSERT INTO @TABLE SELECT 12,'tahi',11
INSERT INTO @TABLE SELECT 13,'chinese',11
INSERT INTO @TABLE SELECT 14,'chinese',6

DECLARE @nodeID INT = 1;
DECLARE @result VARCHAR(MAX);

;WITH Recursives AS (
        SELECT cat_id, main_cat_id
        FROM @TABLE
        WHERE Cat_Id = @nodeID
        UNION ALL
        SELECT T.cat_id, T.main_cat_id
        FROM @TABLE AS T 
        INNER JOIN Recursives AS R 
            ON  t.Main_Cat_Id = r.cat_id
)
INSERT INTO @Collected
SELECT cat_id
FROM Recursives


SELECT @result = STUFF(
        (SELECT ',' + CAST( cat_id AS VARCHAR)
        FROM @Collected
        ORDER BY cat_id
        FOR XML PATH('')
        ), 1,1,'')

SELECT @result

Answer 3

, , 1 1:

WHILE 1 =1

1=1 , , break .

, Microsoft T-SQL. , .

open , fetch next, , while @@FETCH_STATUS = 0 (0 ).

:

IF @@FETCH_STATUS <> 0

@Category , . , , .

@Category, ; , , . , .

, , , , , , Adriaan .