facing probs in nested cursor
-
Hi i am facing problem in nested cursor..., In this first cursor for managers and second one is for employee details..., Its going on well.., But the employee detail it binding the all the managers, last record of employee at 2 times..., but i want to bind those also at one time..., See i given my procedure on down side: pLz tell me what wrong in that?, DECLARE curMgr CURSOR FOR SELECT distinct ManagerID FROM @tbl_TempManagers OPEN curMgr FETCH NEXT FROM curMgr INTO @mgID Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid WHILE (@@FETCH_STATUS = 0 ) BEGIN SET @EmpDetail = '' SELECT @Count=Count(*) FROM @tbl_TempEmployee -- SET @Counter=1 DECLARE curDetailList CURSOR FOR SELECT distinct EmployeeName from @tbl_Tempemployee where ManagerId = @mgID OPEN curDetailList FETCH NEXT FROM curDetailList INTO @EmployeeName1 SELECT convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description], TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date], CreationBy as [Name Of Employee] FROM tbl_TSEnterData where CreationBy=@employeename1 AND TSDate BETWEEN CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) order by CreationBy asc,TSDate Desc WHILE (@@FETCH_STATUS = 0 AND @Counter<@Count) BEGIN SET @EmpDetail = @EmpDetail + @EmployeeName1 + ', ' FETCH NEXT FROM curDetailList INTO @EmployeeName1 SELECT convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description], TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date], CreationBy as [Name Of Employee] FROM tbl_TSEnterData where CreationBy=@employeename1 AND TSDate BETWEEN CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) order by CreationBy asc,TSDate Desc SET @counter=@counter+1 END CLOSE curDetailList DEALLOCATE curDetailList INSERT INTO @Result VALUES (@mgID, @EmpDetail) FETCH NEXT FROM curmgr INTO @mgID Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid END CLOSE curmgr DEALLOCATE curmgr
Thanks & Regards, NeW OnE, please don't forget to vote on the post
modified on Wednesday, July 16, 2008 2:52 AM
-
Hi i am facing problem in nested cursor..., In this first cursor for managers and second one is for employee details..., Its going on well.., But the employee detail it binding the all the managers, last record of employee at 2 times..., but i want to bind those also at one time..., See i given my procedure on down side: pLz tell me what wrong in that?, DECLARE curMgr CURSOR FOR SELECT distinct ManagerID FROM @tbl_TempManagers OPEN curMgr FETCH NEXT FROM curMgr INTO @mgID Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid WHILE (@@FETCH_STATUS = 0 ) BEGIN SET @EmpDetail = '' SELECT @Count=Count(*) FROM @tbl_TempEmployee -- SET @Counter=1 DECLARE curDetailList CURSOR FOR SELECT distinct EmployeeName from @tbl_Tempemployee where ManagerId = @mgID OPEN curDetailList FETCH NEXT FROM curDetailList INTO @EmployeeName1 SELECT convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description], TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date], CreationBy as [Name Of Employee] FROM tbl_TSEnterData where CreationBy=@employeename1 AND TSDate BETWEEN CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) order by CreationBy asc,TSDate Desc WHILE (@@FETCH_STATUS = 0 AND @Counter<@Count) BEGIN SET @EmpDetail = @EmpDetail + @EmployeeName1 + ', ' FETCH NEXT FROM curDetailList INTO @EmployeeName1 SELECT convert(varchar(20),TSDate,101) as [Date],TSSubject as [Subject],TSDescription as [Description], TSHour as [Hour],TSMin as [Minute],convert(varchar(20),CreationDate,101) as [Creation Date], CreationBy as [Name Of Employee] FROM tbl_TSEnterData where CreationBy=@employeename1 AND TSDate BETWEEN CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AND CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) order by CreationBy asc,TSDate Desc SET @counter=@counter+1 END CLOSE curDetailList DEALLOCATE curDetailList INSERT INTO @Result VALUES (@mgID, @EmpDetail) FETCH NEXT FROM curmgr INTO @mgID Insert Into @tbl_TempEmployee Select MId,ID,UserName,EId from tbl_login Where Mid=@mgid END CLOSE curmgr DEALLOCATE curmgr
Thanks & Regards, NeW OnE, please don't forget to vote on the post
modified on Wednesday, July 16, 2008 2:52 AM
I have trouble understanding your question so I can't provide much actual help; however looking at the query one thing puzzles me. Why do you have similar selects both outside your "WHILE" loops as well as inside? To me it looks like it will cause problems with the loop over FETCH NEXT. Removing duplicate functionality could help you reduce the complexity of your query making it more manageable, and it could possible be from those double selects that your problems arise.
--------------------------- Blogging about SQL, Technology and many other things