title: Getting rows affected in stored procedure INSERT statement with NOCOUNT OFF
body: <p>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?</p>
  
  <pre><code>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;
  </code></pre>