sql - 我正在使用游标从临时表中获取行，但如果条件不起作用，这是在 MSSQL 上执行此类任务的好方法吗？

Question

我有一个具有记录 id、模块 id 和状态 id 的临时表，现在我必须检查该临时表的每一行，并且根据@levelid 我必须使用该状态 id 更新相应的表，但下面的代码永远不会去 else if条件，我是 mssql 的新手，寻求您的宝贵答案:)

DECLARE @MyCursor CURSOR; 
DECLARE @LevleId INT, 
        @STATUS  VARCHAR(max), 
        @RECID   VARCHAR(max); 

BEGIN 
    SET @MyCursor = CURSOR 
    FOR SELECT TOP 1000 level_id, 
                        status_id, 
                        record_id 
        FROM   #temp_record_id_typ2 
        WHERE  approval_typ_id = 2 

    OPEN @MyCursor 

    FETCH next FROM @MyCursor INTO @LevleId, @STATUS, @RECID 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          IF ( @LevleId = 1 
               AND @STATUS != 3 ) 
            BEGIN 
                SELECT @Update_Typ2_Rec = N'UPDATE hrms_approvals  SET approval_status_id = ' +
                       @STATUS 
                         + ' WHERE record_id = ' + @RECID 
                         + ' AND module_id = ' + @Module_id + 
                       ' '; 

                --SELECT @Update_Typ2_Rec 
                EXECUTE Sp_executesql 
                  @Update_Typ2_Rec; 

                SELECT @UPDATE_MODULE_TABLE = N'UPDATE ' + @TableName + 
                                              '  SET approval_status_id = 9  WHERE id = ' 
                                              + @RECID + ''; 

                --SELECT @UPDATE_MODULE_TABLE 
                EXECUTE Sp_executesql 
                  @UPDATE_MODULE_TABLE; 

                PRINT 'LevleId = 1' 
            END 
          ELSE IF ( @LevleId = 2 
               AND @STATUS != 3 ) 
            BEGIN 
                SELECT @Update_Typ2_Rec = 
                       N'UPDATE hrms_approvals  SET approval_status_id = ' 
                       + @STATUS + ' WHERE record_id = ' 
                       + @RECID + ' AND module_id = ' 
                       + @Module_id + ' '; 

                --SELECT @Update_Typ2_Rec 
                EXECUTE Sp_executesql 
                  @Update_Typ2_Rec; 

                SELECT 
            @UPDATE_MODULE_TABLE = N'UPDATE ' + @TableName 
                                   + '  SET approval_status_id = 10  WHERE id = ' + @RECID + ''; 

                --SELECT @UPDATE_MODULE_TABLE 
                EXECUTE Sp_executesql 
                  @UPDATE_MODULE_TABLE; 

                PRINT 'LevleId = 2' 
            END 
          ELSE IF ( @LevleId = 3 
               AND @STATUS != 3 ) 
            BEGIN 
                SELECT @STATUS, 
                       @LevleId 

                PRINT 'LevleId = 3' 
            END 
          ELSE IF ( @LevleId = 4 
               AND @STATUS != 3 ) 
            BEGIN 
                SELECT @STATUS, 
                       @LevleId 

                PRINT 'LevleId = 4' 
            END 
          ELSE IF ( @LevleId = 5 
               AND @STATUS != 3 ) 
            BEGIN 
                SELECT @STATUS, 
                       @LevleId 

                PRINT 'LevleId = 5' 
            END 

          FETCH next FROM @MyCursor INTO @LevleId, @STATUS, @RECID 
      END; 

    CLOSE @MyCursor; 

    DEALLOCATE @MyCursor; 
END; 

标签： sqlsql-server

Answer 1

试试下面的代码，你没有声明这个参数“ @TableName和@ModuleId ”，检查你的代码并为@TableName和@ModuleId赋值

Declare @Counter int=1
        ,@Tot_Count int=0
        ,@LevleId int 
        ,@Status int
        ,@RecId int
        ,@Module_id int
        ,@TableName varchar(30)=''

Create Table #Temp_Table
(
    Id int identity(1,1)
    ,level_id int
    ,status_id int
    ,record_id int
    ,Module_id int
)

Insert into #Temp_Table
(
    level_id,status_id,record_id
)
 Select level_id
        ,status_id
        ,record_id 
        ,Module_id
   from #temp_record_id_typ2
  where approval_typ_id = 2 

Select @Tot_Count=Count(1) from #Temp_Table

While @Tot_Count >= @Counter
Begin

         Select @LevleId=level_id 
                ,@Status=status_id
                ,@RecId=record_id
                ,@Module_id=Module_id
           from #Temp_Table
          where Id=@Counter

          IF ( @LevleId = 1 AND @STATUS <> 3 ) 
            BEGIN 

                Exec
                ('
                     UPDATE hrms_approvals  
                        SET approval_status_id = ' + @STATUS + ' 
                      WHERE record_id = ' + @RECID + ' 
                        AND module_id = ' + @Module_id +' 

                     UPDATE '+ @TableName +'  
                        SET approval_status_id = 9  
                      WHERE id = '+ @RECID + '
                ') 

                PRINT 'LevleId = 1' 
            END
            ELSE IF ( @LevleId = 3 AND @STATUS <> 3 ) 
            BEGIN 
                    SELECT  @STATUS, 
                            @LevleId 

                    PRINT 'LevleId = 3' 
            END 
            ELSE IF ( @LevleId = 4 AND @STATUS <> 3 ) 
            BEGIN 
                    SELECT @STATUS, 
                           @LevleId 

                    PRINT 'LevleId = 4' 
            END 
            ELSE IF ( @LevleId = 5 AND @STATUS <> 3 ) 
            BEGIN 
                    SELECT @STATUS, 
                           @LevleId 

                    PRINT 'LevleId = 5' 
            END 

         Set @Counter+=1

End

Drop table #Temp_Table