#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