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

或者甚至更好，不要使用游标（这可以作为选择执行，但我将其留给您...只是想展示如何使用游标光标和表作为返回值）

Answer 2

未经测试，这应该可以在不使用游标的情况下工作：

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

Answer 3

USE [Demo_03]
GO
/****** Object:  StoredProcedure [dbo].[SP_Salrafer_BiACT_SOr]    Script Date: 06-Apr-2021 11:32:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[SP_SaTsfer_Bind_SOthr]
(
@SalaryDate DATE=NULL
)
AS

BEGIN

DECLARE 
@EmpCode bigint=null,
   



CREATE TABLE #TempSalaDetail
(
    EmpCode bigint,
    

)


/**End Region**/
/**Create Temp Table For Fetch Billing Details For Reports**/
CREATE TABLE #TempFinalSalaryDetail
(   
    EmpCode bigint,
    EmpName nvarchar(500),
    
    
    
)
/**End Region**/



DECLARE @TempSiteCursor CURSOR
DECLARE @TempSiteFinalCursor CURSOR



INSERT INTO #TempSalaryDetail
(
    EmpCode
)
(

SELECT EmpCode,
FROm tbl_SalRegi WHERE 

)



    
SET @TempSiteFinalCursor=CURSOR FOR SELECT EmpCodeFROM #TempSalaryDetail
OPEN @TempSiteFinalCursor
FETCH NEXT FROM @TempSiteFinalCursor INTO @EmpCode

WHILE @@FETCH_STATUS=0
    BEGIN
        
        
        
        

        
        SET @AccountNo=(SELECT AccountNo FROM tbl_Emps_Account WHERE EmpAccId=@EmpAcId )
        

        

    
        INSERT INTO #TempFinalSalaryDetail
        (       
            EmpCode,
            
            
        )
        VALUES
        (
         
          @EmpCode,
          
        
         
        )
    
    FETCH NEXT FROM @TempSiteFinalCursor INTO @EmpCode,
    END
    
SELECT  EmpCode,
            EmpName,
            
FROM #TempFinalSalaryDetail
order by case when BankId=1 then 0 else 2 end, BankId asc, EmpCode asc


DEALLOCATE @TempSiteFinalCursor

DROP TABLE #TempSalaryDetail
DROP TABLE #TempFinalSalaryDetail
END