November 12, 2008 at 2:45 am
Hi Experts,
I am working on a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
For getting count for any member I have a stored procedure which calls user defined function. The user defined function calls the same function for getting the leg count of any member who logs in and wishes to see leg count.
Every thing works fine till a member reaches 950 members below him but after more than 950 members get registered below a member (on either leg) the nesting error is shown when a member clicks on one's leg count link provided to see leg count.
Please advise i have a deadline to meet..
Stored procedure is as follows:
CREATE Procedure GetLegCount_forBSGUser
@user-id int
AS
select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'
GO
User defined function is:
CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @user-id int)
RETURNS @retFindReports TABLE (UserID int, Name varchar(50), ReferID int, LegType varchar(1), Status varchar(1))
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT userid,username,referid,PositionType,Status FROM BSGUserregister WHERE userID=@UserID
END
DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int, @Report_LegType varchar(1), @Report_Status varchar(1)
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE referID=@UserID union
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE parentID=@UserID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID,@Report_LegType, @Report_Status
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)
INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_Name, @Report_BossID, @Report_LegType,@Report_Status)
FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID, @Report_LegType,@Report_Status
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
And have already tried using CTE but seems it doesnt work on sql 2000. Please Advise...
November 12, 2008 at 7:21 am
TOR (11/12/2008)
And have already tried using CTE but seems it doesnt work on sql 2000. Please Advise...
Please post SQL 2000 related problems in the SQL 2000 forums. If you post them in the 2005 forums, you will get solutions that are specific to SQL 2005, wasting your time and the time of the people that reply to you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2008 at 4:43 am
Gail:
Am sorry Gail but i thought this to be a Sql Server 2000 Forum
November 13, 2008 at 4:56 am
You originally posted in in one of the SQL 2005 forums. I asked the moderator to move it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply