Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-12-22 : 07:50:42
|
| ThePureNuts writes "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 holidaysDECLARE 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" |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-22 : 12:08:32
|
| What does usp_NestedSelQuestionnaire do? You should post the rest of this sp, not just the cursor loop. |
 |
|
|
|
|
|
|
|
Alternative to cursor