Make a recursive function in SQL Server 2005

Question

cat_id  prod_name parent_cat_id
------ ---------- ------------
1   prod_1    2
2   prod_2    5
3   prod_3    1
4   prod_4    3
5   prod_5    7
6   prod_6    5

In a recursive function, make a table and by using these, if cat_id = 1 and parent_cat_id = 1 take that product name and if that product category id and parent category id is same then take that record also..

ANS IS LIKE :::

1   prod_1    2
2   prod_2    5
5   prod_5    7

Answer 1

WITH    rows AS
        (
        SELECT  cat_id, prod_name, parent_cat_id
        FROM    mytable
        WHERE   cat_id = 1
        UNION ALL
        SELECT  m.cat_id, m.prod_name, m.parent_cat_id
        FROM    mytable m
        JOIN    rows r
        ON      r.parent_cat_id = m.cat_id
        )
SELECT  *
FROM    rows

Answer 2

I Found it by R & D..

ALTER FUNCTION dbo.recu_tab
(
    @cat_id INT
)
RETURNS @tb_product TABLE
      (
       cate_id INT,
       cate_name VARCHAR(20),
       par_cate_id INT
      ) 

AS
BEGIN
     DECLARE @Category_id INT
     DECLARE @Category_name VARCHAR(20)
     DECLARE @Par_Category_id INT

     DECLARE db_rec CURSOR FOR
     SELECT Category_id,Category_name,Par_Category_id FROM product123 WHERE category_id = @cat_id

     OPEN db_rec
     FETCH NEXT FROM db_rec INTO @Category_id,@Category_name,@Par_Category_id

     WHILE @@FETCH_STATUS = 0
     BEGIN

      INSERT INTO @tb_product
         SELECT @Category_id,@Category_name,@Par_Category_id

      INSERT INTO @tb_product SELECT cate_id,cate_name,par_cate_id
      FROM dbo.recu_tab(@Par_Category_id)

      FETCH NEXT FROM db_rec INTO @Category_id,@Category_name,@Par_Category_id

     END
     CLOSE db_rec
     DEALLOCATE db_rec

     RETURN
END

SELECT * FROM dbo.recu_tab(2)