storedproc loop problem - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

storedproc loop problem

Author

Topic

crownclit
Starting Member

20 Posts

Posted - 2015-01-20 : 06:50:02

Hi Guys I need some help in here. I have a family database with one member table. This table contains members from different branches distinguish by a column [Branch] int. When I add or update a family member [FatherID] column I will fire a trigger which will run storedproc to update a branch number for all children and grand children or grand grand children of the oldest member in each branch.

First I get a list of the oldest members (they have FatherID = 0) and get their Branch number. Then I will loop and update each child and grand child with that Branch number.
Problem 1 – I cant test my stored proc cause of the error –

Msg 16915, Level 16, State 1, Procedure usp_UpdMemberBranch, Line 7
A cursor with the name 'curNode' already exists.
The statement has been terminated.

Don't know how to fix that.

Previously I had different error: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). But I can not replicate this anymore.

Could you please help? Is my logic correct? Here is my code
-------------------------------
ALTER PROCEDURE [dbo].[usp_UpdMemberBranch]
AS
DECLARE @BranchNo INT

BEGIN
DECLARE curNode CURSOR
FOR SELECT [Branch] FROM [dbo].[tblFamilyMember] WHERE [FatherID] = 0 ORDER BY [Branch] ASC
OPEN curNode
FETCH NEXT FROM curNode INTO @BranchNo
WHILE @@fetch_status = 0
BEGIN
WHILE(1=1)
BEGIN
UPDATE [dbo].[tblFamilyMember] SET [Branch] = @BranchNo
FROM [dbo].[tblFamilyMember]
WHERE [dbo].[tblFamilyMember].[FatherID] in (SELECT [FamilyMemberID] FROM [dbo].[tblFamilyMember] WHERE [Branch] = @BranchNo )
END
FETCH NEXT FROM curNode INTO @BranchNo
END
CLOSE curNode
DEALLOCATE curNode
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-20 : 06:58:39

don't use a cursor.

crownclit
Starting Member

20 Posts

Posted - 2015-01-20 : 20:52:24

quote:
Originally posted by gbritton

don't use a cursor.

if not a cursor, could please you provide me a code mockup so i can understand.
thanks
CC

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-21 : 02:42:33

you have used WHILE(1=1) in your stored procedure. This doesn't have an end in this given code. Please change the expression and try

Regards
Viggneshwar A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-21 : 08:58:54

try something like this"


update fm
   set Branch = fm2.branch
FROM dbo.tblFamilyMember fm
join dbo.tblFamilyMember fm2
  on fm.fatherid = fm2.FamilyMemberID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources