Cannot execute procedure from a procedure

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 2000 Forums

SQL Server Development (2000)

Cannot execute procedure from a procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-29 : 06:29:44

Kaz writes "For some reason SQL Server 2K wont let me execute a procedure from a procedure. @@NESTLEVEL isn't even close to 32. I look at the trace and all the EXEC commands within the procedure show up as blank. Any clues?

CREATE PROCEDURE RemoveGroup
@Id INTEGER
AS

PRINT @@NESTLEVEL;

DECLARE GroupChildren CURSOR LOCAL FOR SELECT groupId FROM MessengerGroup WHERE groupParent = @Id
DECLARE @CurrentGroup INTEGER

OPEN GroupChildren;
FETCH NEXT FROM GroupChildren INTO @CurrentGroup;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC RemoveGroup GroupChildren;
FETCH NEXT FROM GroupChildren INTO @CurrentGroup;
END
CLOSE GroupChildren;
DEALLOCATE GroupChildren;

-- gone down as far as it can

IF ((SELECT groupGroupMember FROM MessengerGroup WHERE groupId = @Id) = 'M') -- member group
BEGIN
DECLARE GroupMembers CURSOR LOCAL FOR SELECT memberId FROM Member WHERE memberGroupId = @Id
DECLARE @MemberId INTEGER

OPEN GroupMembers
FETCH NEXT FROM GroupMembers INTO @MemberId;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC RemoveMemberId @MemberId;
FETCH NEXT FROM GroupMembers INTO @MemberId;
END

CLOSE GroupMembers;
DEALLOCATE GroupMembers;
END

DELETE FROM MessengerGroup WHERE groupId = @Id; -- delete the group"

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-29 : 06:47:25

quote:
Originally posted by AskSQLTeam

EXEC RemoveGroup GroupChildren;

I have an doubt.Is it possible to give parameter value as "Cursor"?
Is it possible to use proedure as "Recursive"?

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-29 : 10:31:49

What does "won't let me" mean? Are you getting an error?

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources