Unanswered: Recursive Data Query(e.g Heading- sub heading)
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
so English is a heading as level is 0 and has no parent as parentId is also 0.
Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
same is the case with writing
where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored
procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
If you know that you will have no more than three levels to your hierarchy, then you are probably better off coding Headings, Subheadings, and Questions as separate tables. The primary purpose of recursive table relationships is to handle cases where the number of levels is unknown and potentially unlimited.
If it is the case that you can have additional levels in your tree, then within TSQL the use of an accumlator table is generally easier to program and more efficient to process than a true recursive query.
An accumulator table is a temporary table that will hold your output dataset. You initially populate the table with a single seed record (the ID submitted as a paramter). You then create a loop that inserts into the accumulator table all the children of records that are already present in the table, unless the child record itself already exists in the table.
When the rowcount returned by the insert statement is 0, you have gathered all the child records and you exit your loop.
If it's not practically useful, then it's practically useless.