This is for recursive calling store procedure for Tree Structure

arumugam007

#create table
CREATE TABLE category(cat_id INT NOT NULL IDENTITY(1,1), category_name VARCHAR(250) NOT NULL,parent_id INT NOT NULL)
#insert datas
INSERT INTO category VALUES('Roman','0')
INSERT INTO category VALUES('South Italian','0')
INSERT INTO category VALUES('Viking','0')
INSERT INTO category VALUES('Printer Supplies & Accessories','0')
INSERT INTO category VALUES('Desktop & Laptop Accessories','0')
INSERT INTO category VALUES('Networking & Communications','0')
INSERT INTO category VALUES('Roman1','1')
INSERT INTO category VALUES('Roman2','1')
INSERT INTO category VALUES('Roman3','1')
INSERT INTO category VALUES('South Italian1','2')
INSERT INTO category VALUES('South Italian2','2')
INSERT INTO category VALUES('South Italian3','2')

#Create store procedure
CREATE PROCEDURE sp_fetch_categories(@parent_id INT)
AS
BEGIN
DECLARE @catid VARCHAR(MAX)
SET @catid = ''
--SET @catid = (SELECT cat_id FROM category WHERE parent_id = @parent_id)
SELECT @catid = @catid + CONVERT(VARCHAR,cat_id) + ',' FROM category WHERE parent_id = @parent_id
--SET @catid = SUBSTRING(@catid, 1, LEN(@catid)-1)
--IF NOT EXISTS (CREATE TABLE #dummy(cat_id INT, category_name VARCHAR(250)))
--BEGIN

IF OBJECT_ID('dummies') IS NULL
BEGIN
CREATE TABLE dummies(cat_id INT, category_name VARCHAR(250))
END

WHILE(CHARINDEX(',',@catid,0)>0)
BEGIN
DECLARE @current_catid VARCHAR(10)
SET @current_catid = SUBSTRING(@catid, 0, CHARINDEX(',',@catid,0))

INSERT INTO dummies
SELECT @current_catid AS cat_id,category_name FROM category WHERE cat_id = @current_catid

EXEC sp_fetch_subcategories @current_catid

SET @catid = SUBSTRING(@catid, CHARINDEX(',',@catid,0)+1,LEN(@catid))
--SELECT @current_catid
--SELECT @catid
END
SELECT * FROM dummies
DROP TABLE dummies
END

#sub store procedure name `sp_fetch_subcategories`

CREATE PROCEDURE sp_fetch_subcategories(@parent_id INT)
AS
BEGIN
DECLARE @catid VARCHAR(MAX)
SET @catid = ''
SELECT @catid = @catid + CONVERT(VARCHAR,cat_id) + ',' FROM category WHERE parent_id = @parent_id
WHILE(CHARINDEX(',',@catid,0)>0)
BEGIN
DECLARE @current_catid VARCHAR(10)
SET @current_catid = SUBSTRING(@catid, 0, CHARINDEX(',',@catid,0))

INSERT INTO dummies
SELECT @current_catid AS cat_id,category_name FROM category WHERE cat_id = @current_catid

EXEC sp_fetch_subcategories @current_catid

SET @catid = SUBSTRING(@catid, CHARINDEX(',',@catid,0)+1,LEN(@catid))
END
END