| Author |
Topic |
|
afterburn
Starting Member
28 Posts |
 Posted - 2004-02-13 : 22:05:44
|
| I have a sp that calls itself recusively. However it seems to be erroring out. Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.The cusror is declare Locally to the SP however once it steps into the self nested procedure it fails. Is there a way to actually call an SP with a cursor in it recursively? And how do I go about doing so.This is fustrating me.... |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-13 : 22:08:25
|
| You have described how you are trying to accomplish something. May I ask what you are trying to accomplish in terms of data? |
 |
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-02-13 : 22:18:53
|
| The data stored like a tree in a single table. If a record is deleted or appended to another parent record i would like to make sure the order is corrected before it gets out of sequence to far an indexes no longer work correctly.Its really just an issue of the cursor scope I beleive. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-13 : 22:27:30
|
| Most Tree and Hierarchy functions can be maintained without the use of cursors and/or recursion.Are you using a nested set model or an adjacency model to represent your tree? |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-02-13 : 22:34:35
|
| Its nested with the Joe Celko info but was working to correct some issues that I see wrong with his base model like removal of records or reasignment to another node. I written all the code to update the data however I wanted to have an idependant ordering schema that was not dependant on the LeftSide,RightSide fields. Rather have the ablility to move the records around like inserting an employee between 2 existing ones creates an issue with his model. Its also holds information about the count of children it holds and the level down in the tree.Seems really scope of the cursor is global to any query that is currently running on that connection. ?? If so how to solve this issue? |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-13 : 22:53:58
|
quote:
Rather have the ablility to move the records around like inserting an employee between 2 existing ones creates an issue with his model
Indeed it does if your sides are stepped by a value of 1 but what happens if you step the values by a larger value ??The following article (Reader Feedback) describes what I am referring to:http://forum.mywuzhen.com/thread.jsp?forum=11&thread=3207&message=10326 |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-02-13 : 23:10:13
|
| What I am creating is a category system. The issue is that the users or data entry people should have to know where the data should be inserted rather that they want it to appear in an specified order. So I allow them to insert their basic data reordering the data to handle the new value. I already have looked at larger incremented values. However you run into the issue of the user wanting to place the item in a middle of records or moves the records ordering. you are left?? The user shouldn't have to know what the base logic in the back is doing.CREATE TABLE [dbo].[tblNavigation] ( [NavID] [int] IDENTITY (1, 1) NOT NULL , [NavName] [varchar] (128) NOT NULL , [ParentNavID] [int] NOT NULL , [Children] [int] NOT NULL , [Level] [int] NOT NULL , [NavOrder] [int] NOT NULL , [LeftSide] [int] NOT NULL , [RightSide] [int] NOT NULL , [ClientNavOrder] [int] NOT NULL , [Tainted] [bit] NOT NULL ) ON [PRIMARY]GONavOrder has the real order in it. Instead of leftside rightside gig.... I can already find how deep it is in the tree. I can get the level of the depth of the tree the record is ... What is occuring is I am placing the values in a temp table then updating the main table however the insert works messed up because of the ordering on the clustered index for leftside rightside. So my thought around this was to step from top down. Cursor comes to mind with a recursive spso you would have output likeNavID,ParentID,NavOrder1,0,12,1,23,2,34,2,4But its likely the data might get deleted and the index again is broken along with the idea of larger steps on the sides. The idea of incremented values is just derived from Binary tree in reality. Just can't seem to get the ordering method to correctly work. |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-02-14 : 14:14:19
|
| The solution to the problem is a not recursive procedure that uses multi-temp tables.The first for pulling the ID and parent. The second hold the NavID,LeftSide,RightSide.And a thrid that holds NavID and an ID field. The issue with a recursive procedure using a cursor is the procedure is not out of scope whenthe procedure calls its self the second time. The fix was Ordering by ParentID and the ClientNavOrder on the first select to the first table.Using the cursor to move thru the records to update leftside,rightside in the loop.Inner joining the temp table and the original to update the leftside ,rightside fields.Once that is complete you can select the navID ordered by the LeftSide to insert into the final table that will be used to update the original table with an update inner join again.I also added to update statements to update children and level fields.Mostlikley going to be removing the Tainted field from the table. Was going to use it to see if any records have modified either the ClientNavOrder or ParentNavID fields using a job to trigger the updates of the leftside rightside. However this an issue with not being in the order when the client goes to view the content. Even with 10K records and 10 levels deep this shouldn't really pose an impact on the server.It mostlikely could be reduced to only re-order the items that are affect by changing a bit of it but didn't really see any need to do that. |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-02-14 : 14:45:37
|
| Local (cursor) means local relatively to the current connection.Try this:create proc qweasdeclare @t varchar(80)declare abc cursor for select town from townsopen abcfetch next from abc into @tprint @treturnGOexec qwedeclare @e varchar(80)fetch next from abc into @eprint @eclose abcdeallocate abc |
|
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-02-14 : 14:58:56
|
| Then again you start to get into the limits of how deep a tree can be come. The issue is scope even if you could get it to work with the way you suggest your limit is 32 levels deep. Limit on how many time a procedure can be nested. I know about the reasons the thing is occuring but the error seemed ambiguous at the least. It seemed to imply that I had changed the amount of parameters that were there. However the error begins to nest itself when the cursor is called again by calling the procedure from within itself. There are no limits on how the data is sorted in the method I described above 1 cursor 3 temp tables 4 updates .... corrects the issue. |
|
|
|
|
cusror scope or is it