SQL Server - 游标

Question

我正在尝试使用游标遍历表:

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

以上返回 taskResourceOID 和 EvUserOID。如果我需要输出一个带有@TaskOID 和各自的 taskResourceOID 和 EvUserOID 的表，最好的方法是什么？

Answer 1

使用临时表或表变量..

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

或者更好的是，不要使用游标(这可以作为选择执行，但我把这留给你...只是想展示如何使用游标和表作为返回值)