December 22, 2006 at 3:31 am
Within a Stored Proc I create a temp table and populate it with Id's I know want to loop around these Id's and call the same function again for each Id. I was using a cursor but the problem is when i make the recursive call, it says that cursor already exists. I have heard that usually where cursors are used they are not needed so I'm looking for another way to loop around each record in a temp table, right now my cursor is as follows. So I'm trying to loop around each subheaderId in #header table. Any help would be greatly appreciated. Happy holidays
DECLARE selQuest Cursor
FOR
SELECT subheaderId FROM #header
Open selQuest
--- We need to make containers for the Cursor Info
DECLARE @hdid Int
Fetch NEXT FROM selQuest INTO @hdid
While (@@FETCH_STATUS <> -1)
BEGIN
print(@hdid)
---INSERT each main headers questions
INSERT INTO ##headerQuest (hdType,headerid, header, subheaderid, subheader, qnid, qnText, ctrlid, ctrltypid, ctrlname, displaytext, seq, mainseq, qnseq)
SELECT a.hdType, a.headerid, a.header, a.subheaderid, a.subheader, b.qnid, b.qnText, b.ctrlid, b.ctrltypid, b.ctrlname, b.displaytext, a.seq,
a.mainseq, b.seq as qnseq
FROM #header a , #qn b
WHERE a.subheaderid = b.headerId AND a.subheaderid = @hdid
--recursive call
EXEC usp_NestedSelQuestionnaire @status, @hdid, @questionnaireId
IF (@@FETCH_STATUS <> -2)
FETCH NEXT FROM selQuest INTO @hdid
END
CLOSE selQuest
DEALLOCATE selQuest"
December 22, 2006 at 5:26 am
Perhaps you could pass the subheader as a parameter as well.
Is this like
Questionnaire
Header
Header_level2
Header_level3...
?
December 22, 2006 at 5:40 am
well yes i pass in a header into the storedProc as a param, now i want to get all the subheaders under that header and then the sub headers under that header etc etc.
So i pass in the header as a param, then get each subheader and store them in the temp table named #header, now i want to loop through that temp table get each headerid and preform the same function again hence the recursion, if this is at all unclear i can post the whole stored Proc yet I feel an explaination + the cursor part is better
Thanks in advance for your help,
Colum
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply