异常处理后，存储过程不返回受影响的行

Question

我在我的存储过程中添加了异常处理，如下所示。

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;
 BEGIN TRY
         BEGIN TRAN
                    UPDATE bud
                    SET bud.BudgetStateID    = @BudgetStateID
                    FROM [dbo].[BudgetOffice] bud
                        INNER JOIN [dbo].[vw_Office] vw
                            ON (vw.OfficeID   = bud.OfficeID)
                        WHERE   vw.DistrictID = @DistrictID
         IF @@ERROR = 0
      BEGIN   
          COMMIT TRAN;
          SELECT @@ROWCOUNT AS AffectedRow;
      END
 END TRY
 BEGIN CATCH
           SELECT @@ERROR AS ERROR
 ROLLBACK TRAN;
 END CATCH
 SET NOCOUNT OFF ;
 END

我需要使用@@ROWCOUNT返回受影响的行数。但是这个stored procedure总是返回rowcount为0。有什么原因吗?我需要把@@rowcount的表述写在update的后面吗?

Answer 1

您需要在UPDATE语句之后选择@@ROWCOUNT。根据文档:

语句，如USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR，BEGIN TRANSACTION或COMMIT TRANSACTION将ROWCOUNT值重置为0。

由于@@ROWCOUNT在COMMIT TRAN之后，@@ROWCOUNT返回0。

Answer 2

你需要将全局变量的结果存储在局部变量中，因为它会在下一个指令后改变，如:

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;
 DECLARE @rowcount INT, @error INT;
 BEGIN TRY
         BEGIN TRAN
                    UPDATE bud
                    SET bud.BudgetStateID = @BudgetStateID
                    FROM [dbo].[BudgetOffice] bud
                    JOIN [dbo].[vw_Office] vw
                      ON vw.OfficeID   = bud.OfficeID
                    WHERE vw.DistrictID = @DistrictID;
      SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;
      IF @error = 0
      BEGIN   
          COMMIT TRAN;
          SELECT @rowcount AS AffectedRow;
      END
 END TRY
 BEGIN CATCH
     SELECT @@ERROR AS ERROR
     ROLLBACK TRAN;
 END CATCH
 END

或者干脆放弃在TRY CATCH块中使用@@ERROR:

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;
 BEGIN TRY
    BEGIN TRAN
           UPDATE bud
           SET bud.BudgetStateID = @BudgetStateID
           FROM [dbo].[BudgetOffice] bud
           JOIN [dbo].[vw_Office] vw
             ON vw.OfficeID   = bud.OfficeID
           WHERE vw.DistrictID = @DistrictID;
           SELECT @@ROWCOUNT AS AffectedRow;
    COMMIT TRAN; 
 END TRY
 BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ERROR
    ROLLBACK TRAN;
 END CATCH
 END

@BudgetStateID在哪里定义?