How to call a recursive function in sql server

Question

I have a table as follows

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 is cat_id of previously added category in which it belongs)

This table is used for the categories the product where veg category has the two sub category main course and starter which is identify by main_cat_id and those subcategories again has sub category as indian and mexican

And this categorization is dependent on the user; he can add more sub categories to indian, mexican also so that he can have any level of categorization

now I have to select all the subcategories of any node like if I take veg i have to select

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

to form the string as 1,2,4,5,3,6,7

to do this i wrote a sql function as

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

but this function keep on executing and not gives the desired output i don't understands what wrong is going on plz help me to get this

Possible duplicate of [SQL recursive query that gets all ancestors of an item](http://stackoverflow.com/questions/16749095/sql-recursive-query-that-gets-all-ancestors-of-an-item) — Pieter Geerkens, Dec 20 '15 at 20:58

score 15 · Answer 1 · answered May 28 '14 at 11:33

You dont need a recursive function to build this, you can use a Recursive CTE for that.

Something like

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

The categories are not fixed. These categories and the level of categories are not fixed. — Rhushikesh, May 28 '14 at 11:52
@Rhushikesh: Read the answer! You are confusing the *sample data* construction needed to match your example with the subsequent solution to the posed problem. — Pieter Geerkens, Dec 20 '15 at 20:56

score 1 · Answer 2 · answered May 28 '14 at 13:22

I am ashamed, but I used @astander scipt to give string result.

First I created data you gave. Second I collect rows which I need And then using XML I put everything in one row (function STUFF removes first comma)

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

score 0 · Answer 3 · edited May 23 '17 at 12:10

Your cursor is looping infinitely because you asked it to keep going until 1 no longer equals 1:

WHILE 1 =1

1=1 is always true so the loop never ends, and you don't explicitly break out of it anywhere.

You would do well to study some examples of cursors, for example this one in the Microsoft T-SQL documentation. They are quite formulaic and the main syntax rarely needs to vary much.

The standard approach after opening the cursor is to do an initial fetch next to get the first result, then open a while loop conditional on @@FETCH_STATUS = 0 (0 meaning successful).

Because you're looking only for unsuccessful cursor fetch states inside your cursor:

IF @@FETCH_STATUS <> 0

The setting of @Category will only happen once the cursor has gone past the last row in the set. I suspect this is exactly what you don't want.

I'm also not sure about the scoping of the @Category variable, since it's an input parameter to the function; I generally create new variables inside a function to work with, but off the top of my head I'm not sure this will actually create a problem or not.

More generally, although I don't totally understand what you're trying to achieve here, a recursive function involving a cursor is probably not the right way to do it, as Adriaan Stander's answer suggests.

How to call a recursive function in sql server

3 Answers3

Linked