N Level Category - stored procedure
-
Can some one help me.
i have one table named "Topic"
and it has the following data
topic_id, topic_title,topic_parent_id
1,ASP.NET,-1
2,PHP,-1
3,Authentication in ASP.NET, 1
4,Authentication in PHP, 2
5,php.ini, 2
6,page time out in php.ini, 5
7,web.config, 1
8,session time out in web.config, 7
9,connection string in web.config, 7
10,connectionString, 9
here topic_main_id shows the parent category id and -1 value shows the main category
now i want to create one stored procedure to retrieve the following result in below format like all related categories come together in hierarchy level see below
topic_id, topic_title
1,ASP.NET
3,Authentication in ASP.NET
7,web.config
8,session time out in web.config
9,connection string in web.config
10,connectionString
2,PHP
4,Authentication in PHP
5,php.ini
6,page time out in php.ini
Regards
-
Lookup "Expanding Hierarchies" in Books Online.
And, if you search this site for "Hierarchy", you'll also find some pretty decent procs/functions for this. If you come across any that use recurrsion, be careful because SQL Server will only do 32 levels of recurrsion and then BOOOOOM!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks dear it helped me lot.
Regards
-
You're welcome...
Not sure what it is but being called "dear" instead of "Jeff" kinda bugs me, especially if you're a guy... must be a language barrier thing
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply