SQL Server - Cursor

Question

I am trying to loop through a table using a cursor:

DEClARE @ProjectOID as nvarchar (100)
DECLARE @TaskOID as nvarchar (100)
DECLARE TaskOID_Cursor FOR
SELECT  TaskOID FROM ProjectOID_Temp
OPEN TaskOID_Cursor
FETCH NEXT FROM TaskOID_Cursor INTO @TaskOID
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT t1.OID as taskResourceOID, t2.OID as EvUserOID
    FROM (select OID, resourceOID from taskresourcehours 
    where projecttaskoid =   @TaskOID) as t1,
    (
     select OID, workerOID
     from Evuser
     where workerOID in
      ( select resourceOID from taskresourcehours where projecttaskoid = @TaskOID )
    ) as t2
    WHERE t1.resourceOID = t2.workerOID
FETCH NEXT FROM TaskOID_Cursor
INTO @TaskOID
END
CLOSE TaskOID_Cursor
DEALLOCATE TaskOID_Cursor

That above returns taskResourceOID and EvUserOID. If I need to output a table with the @TaskOID and the respective taskResourceOID and EvUserOID, what is the best way to do it?

Solution 1

Use a temporary table or a table variable..

DEClARE @ProjectOID as nvarchar (100)
DECLARE @TaskOID as nvarchar (100)
DECLARE @retTable TABLE (
  TaskOID nvarchar(100),
  TaskResourceOID nvarchar(100),
  EvUserOID nvarchar(100)
)
DECLARE TaskOID_Cursor CURSOR FOR
SELECT  TaskOID FROM ProjectOID_Temp
OPEN TaskOID_Cursor
FETCH NEXT FROM TaskOID_Cursor INTO @TaskOID
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @retTable
      SELECT @TaskOID,t1.OID as TaskResourceOID, t2.OID as EvUserOID
      FROM (
        select OID, resourceOID from taskresourcehours 
        where projecttaskoid =   @TaskOID) as t1,
        ( 
          select OID, workerOID
          from Evuser
        and workerOID in -- this must be AND and not a second WHERE
          ( select resourceOID from taskresourcehours where projecttaskoid = @TaskOID )
        ) as t2
      WHERE t1.resourceOID = t2.workerOID
FETCH NEXT FROM TaskOID_Cursor
INTO @TaskOID
END
CLOSE TaskOID_Cursor
DEALLOCATE TaskOID_Cursor

SELECT * FROM @retTable

Or even better, don't use a cursor (this can be performed as a select, but I leave this up to you... Just wanted to show how to use a cursor AND a table as return value)

Solution 2

Without having tested, this should work without using a cursor:

DECLARE @retTable TABLE (
  TaskResourceOID nvarchar(100),
  EvUserOID nvarchar(100)
)

INSERT INTO @retTable
SELECT 
    t1.OID as taskResourceOID, 
    t2.OID as EvUserOID
FROM 
  ProjectOID_Temp pt,
  (select OID from taskresourcehours where projecttaskoid = pt.TaskOID) as t1,
  (select OID from Evuser where workerOID in (select resourceOID from taskresourcehours where projecttaskoid = pt.TaskOID)) as t2
WHERE t1.resourceOID = t2.workerOID

Comments

[RK Kuppala]

Hi Matten - this is part of a multi step approach to fix some errors in the data as per the application vendor guidelines. Is there a way to insert @TaskOID too to the output table along with TaskResourceOID and EvUserOID. I am looking for a temp table which has TaskOID|TaskResourceOID|EvUserOID

[Matten]

@RK Kuppala no problem, just add it to the table (i've adopted my answer to your edit)