How to call a recursive function in sql server

sql-function, sql-server, sql-server-2008

Solution

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

Problem

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

Original source

Related problems