Nested Cursor 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 2005 Forums

Transact-SQL (2005)

Nested Cursor Problem

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-17 : 08:03:16

Hi i am getting probs in nested cursor...

Outer cursor i am getting managers...,

Inner cursor based on managers getting employee details...,

In this for all the managers the last employee records i am getting more than one time...,

This is my cursor:

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 order by EmployeeName

OPEN curDetailList

FETCH NEXT FROM curDetailList INTO @EmployeeName1

SELECT dbo.tbl_Login.MID,
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
INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName

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 dbo.tbl_Login.MID,

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

INNER JOIN
dbo.tbl_Login ON dbo.tbl_TSEnterData.CreationBy = dbo.tbl_Login.UserName

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 08:14:12

i don't have the foggiest what you're trying to do or why you're using a cursor to do it. perhaps you could try and explain your requirement? and some sample data would help illustrate what your end result should be

Em

Mageshkumar
Starting Member

29 Posts

Posted - 2008-07-17 : 09:23:01

First cursor for to get managers id,
Second one is for based on particular manager, his/her employee details...,

Actuall o/p have to be like this:

Managerid Date EID EmplName
22 7-2-2008 100 Name1
22 7-3-2008 100 Name1

Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2

Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3

Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4

But,I am getting o/p of that cursor like this:

Managerid Date EID EmplName
22 7-2-2008 100 Name1
22 7-3-2008 100 Name1

Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2

Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2

Managerid Date EID EmplName
22 7-12-2008 101 Name2
22 7-23-2008 101 Name2

Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3

Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3

Managerid Date EID EmplName
23 7-22-2008 103 Name3
23 7-23-2008 103 Name3

Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4

Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4

Managerid Date EID EmplName
24 7-12-2008 104 Name4
24 7-30-2008 104 Name4

See that all the managers last employee record coming more than one time

Becoz of cursor loop i am getting like this...,

How to solve this.......

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 09:27:44

maybe i'm really missing the opint here but why can't you just join yuor managers to employees on managerID ? what is that Date for in your result set?
show us what your tables look like too, with sample data

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources