Help with dynamic SQL with nested cursor

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

Transact-SQL (2008)

Help with dynamic SQL with nested cursor

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-05 : 16:11:04

I have the following query which gives me error "is not a valid identifier" How do I form this query correctly?

Thanks

DECLARE Dept_Cursor cursor FOR
SELECT DepartmentId, Department,DepartmentNameFROM #DeptEmp
order by PercentRates DESC --Ranking
OPEN Dept_Cursor
FETCH NEXT FROM Dept_Cursor INTO @DeptId, @Type,@Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DeptEmp_Cursor cursor FOR
SELECT DepartmentId FROM #DeptEmp
WHERE DepartmentId = @DeptId
order by Ranking
OPEN DeptEmp_Cursor
FETCH NEXT FROM DeptEmp_Cursor INTO @DeptId
WHILE @@FETCH_STATUS = 0
IF (@EmployeeId IS NOT NULL)
BEGIN
SET @SQLQ = N'
INSERT INTO #DeptEmpT
SELECT DepartmentId,Department,DepartmentName FROM #DeptEmp
WHERE DepartmentId = @DeptId
INSERT INTO #DeptEmpT
SELECT v.DepartmentId,v.DepartmentName,v.Name FROM DView v
INNER JOIN EO e
ON v.Id = e.Id
INNER JOIN Employees l On e.Id = l.Id
WHERE v.DepartmentId = ' +CAST(@DeptId AS VARCHAR)+'
AND e.id IN( ' + @EmployeeId +')'
EXEC @SQLQ
FETCH NEXT FROM DeptEmp_Cursor INTO @DeptId

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-05 : 16:15:38

The whole purpose of having the dynamic query inside the cursor is the following statement:
AND e.id IN( ' + @EmployeeId +')'
Here @EmployeeId is a comma delimited string

Thanks.

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-05 : 16:39:50

I found the problem. The statement:
EXEC @SQLQ
should have been wrapped with parenthesis
EXEC (@SQLQ)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 16:42:54

Dare I ask what you need cursors, especially nested cursors, for?

rama108
Posting Yak Master

115 Posts

Posted - 2012-09-05 : 18:34:07

I use cursors because I don't know any better. What is the alternative to the nested cursors that I am using?

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 21:59:24

Would need to see the full code. I can tell from what you've posted so far that the DeptEmp_Cursor is completely redundant, unless what you've posted is incorrect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources