获取NOCOUNT OFF的存储过程INSERT语句中受影响的行

Question

我在SQL Server 2014中有一个存储过程，我首先从表中删除数据，然后执行另一个存储过程，如下所示。我不关心这段代码中受影响的行，所以我使用SET NOCOUNT on。然后执行Insert语句，首先设置SET NOCOUNT OFF。我希望存储过程能够自动返回INSERT中受影响的行，但事实证明并非如此。我搜索了SO，发现了@@ROWCOUNT，这正在工作。然而，这真的有必要吗?为什么下面代码中的INSERT语句不返回受影响的记录呢?

ALTER PROCEDURE [dbo].[SaveTextSetting]
    @UserId INT ,
    @SettingType INT = 0 ,
    @SettingValue NVARCHAR(MAX)
AS
    BEGIN
        BEGIN
            SET NOCOUNT ON;
            DELETE  FROM Settings
            WHERE   UserId = @UserId
                    AND SettingType = @SettingType;
            EXEC dbo.PurgeExpiredSettings;
        END;
        BEGIN
            SET NOCOUNT OFF;
            INSERT  INTO dbo.Settings
                    ( UserId ,
                      SettingsText ,
                      SettingType
                    )
            VALUES  ( @UserId ,
                      @SettingValue ,
                      @SettingType
                    );
            RETURN @@ROWCOUNT; --without this we don't get the rows affected
        END;
    END;

Answer 1

你确定吗?

CREATE PROCEDURE spTest AS RETURN
GO
ALTER PROCEDURE spTest
AS
BEGIN
    SET NOCOUNT OFF
    DECLARE @rt int
    CREATE TABLE #t (
        v int NOT NULL
    )
    INSERT INTO #t (v)
    SELECT v FROM (VALUES (1), (1), (2)) AS t (v) 
    SET @rt = @@ROWCOUNT
    DROP TABLE #t
    RETURN @rt
END
GO
DECLARE @rt int
EXEC @rt = spTest
SELECT @rt
GO
DROP PROCEDURE spTest
GO

Answer 2

我将使用类似.....的输出参数

ALTER PROCEDURE [dbo].[SaveTextSetting]
    @UserId       INT ,
    @SettingType  INT = 0 ,
    @SettingValue NVARCHAR(MAX),
    @RowCount     INT             OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
   DELETE  FROM Settings
   WHERE   UserId = @UserId
     AND   SettingType = @SettingType;
  EXEC dbo.PurgeExpiredSettings;
  INSERT  INTO dbo.Settings( UserId ,SettingsText ,SettingType)
  VALUES  ( @UserId ,@SettingValue ,@SettingType);
      SET @RowCount=  @@ROWCOUNT; 
END