Getting rows affected in stored procedure INSERT statement with NOCOUNT OFF

1
votes

I have a stored procedure in SQL Server 2014 where I first delete data from a table and execute another stored procedure as shown below. I don't care about rows affected on this code so I use SET NOCOUNT ON. I then perform an Insert statement first setting SET NOCOUNT OFF. I would expect the stored procedure to return the rows affected automatically from the INSERT but that has not proven to be the case. I searched SO and found the @@ROWCOUNT and that is working. However, is that really required? Why wouldn't the INSERT statement in the code below return the records affected?

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;
2
sql-servertsqlstored-proceduressql-server-2014
Because the return value from a stored procedure is not intended to return a value. It returns a status code that indicates the status of the execution. If you want to return a value you should use an OUTPUT parameter instead. – Sean Lange
"Why wouldn't the INSERT statement in the code below return the records affected?" 1) If you had two statements with nocount off, which value should SQL Server return? 2) It is returning it, just not via the return value. You can see this if you run the procedure in SSMS. – Shannon Severance
@SeanLange - Technically the return value of a stored proc is just an integer. The values of that integer and what they mean are up to the developer. So it could very easily be the number of rows affected. – CodeNaked
The NOCOUNT setting does not affect @@ROWCOUNT, it determines whether SQL returns a message indicating the number of rows affect to the client (i.e. the '(X row(s) affected)` you see in the Messages tab of the Management Studio). Where are you not getting the rows affected? In the code that calls this stored proc? – CodeNaked
@CodeNaked yes the RETURN_VALUE is an integer but in practice and by MS documentation it is intended to return a status code indicating execution status. msdn.microsoft.com/en-us/library/ms188655.aspx This of course could be used to return the number of rows affected but is non-standard. – Sean Lange

2 Answers

2
votes

Are you sure?

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
1
votes

I would use an output param something like.....

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