sql - 尝试 block 内的@@ RowCount无法返回正确的值--SQL Server

Question

我有一个stored procedure，如果给定记录存在，它将从表中删除记录。

CREATE PROCEDURE [dbo].[USP_DTEST]
@CfgKey   AS VARCHAR(100)  
AS
BEGIN 
SET NOCOUNT ON;

   BEGIN TRY
     BEGIN TRAN
    --Deletes ConfigSetting table if data exists
    IF EXISTS
    (
    SELECT 1
    FROM [dbo].[ConfigSetting]
    WHERE CfgKey  = @CfgKey
     AND RowStatus = 'A'
    )
    BEGIN
        DELETE 
        FROM [dbo].[ConfigSetting]
        WHERE  CfgKey  = @CfgKey
          AND RowStatus = 'A' 
    END


    IF @@ERROR = 0
             BEGIN
                  SELECT 'Number of Records Deleted : '+ CAST(@@rowcount AS varchar(10))
                  COMMIT TRAN;
              END
    END TRY
    BEGIN CATCH
             SELECT @@ERROR AS ERROR, ERROR_LINE() AS [Error Line], ERROR_MESSAGE() AS [Error Message] 
    ROLLBACK TRAN;
    END CATCH

SET NOCOUNT OFF;
END

stored procedure运行正常。但是当我执行sp时 @@rowcount总是返回 0。如果我将 @@rowcount放在 error handling块上方，它将返回1.为什么？此 query中有语法错误吗？

谢谢

Answer 1

看这个演示。删除后应立即存储@@rowcount。正如ZLK在他的评论中所说，@@rowcount将被包括IF在内的任何其他Sql语句重置。

create table demo (n int);
insert demo(n) values (1), (2);
declare  @rc int =-1;

delete from demo where n=1;
set @rc = @@rowcount;
if (@@error = 0) -- this will reset @@rowcount
  select @rc, @@rowcount -- returns  1,  0