Getting No of records inserted in Transaction

Question

I need to get the no of records affected from the stored procedure. Based on this value I want to update the user whether the action got completed.

But my OUTPUT @RecordsAffected parameter is always 0

How to get the no of records inserted in the transaction?

Followed How can I get the number of records affected by a stored procedure? and http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/

This is my procedure

ALTER PROCEDURE [dbo].[SaveRoleFeatures]          
(        
@RoleId INT,        
@SelectedFeatures AS IdInt READONLY,
@RecordsAffected INT OUTPUT      
)       
AS      

BEGIN  

set nocount on;

DECLARE     @ErrorCode  int  
SELECT @ErrorCode = @@ERROR
declare @trancount int;
set @trancount = @@trancount;

BEGIN TRY     

 BEGIN TRAN

     DELETE FROM RoleXFeature WHERE RoleIid= @RoleId  

     INSERT RoleXFeature      
      (      
       RoleIid,      
       FeatureId,      
       IsDeleted  
      )      
      SELECT      

       @RoleId,      
       Id,
       0    

      FROM  @SelectedFeatures 

COMMIT TRAN

              SET @RecordsAffected = @@ROWCOUNT

  END TRY

  BEGIN CATCH

declare @error int, @message varchar(4000), @xstate int;
    select @error = ERROR_NUMBER(),
             @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
    if @xstate = -1
        rollback;
    if @xstate = 1 and @trancount = 0
        rollback
    if @xstate = 1 and @trancount > 0
        rollback transaction SaveRoleFeatures;

    raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    return;

  END CATCH


  END

Try moving `SET @RecordsAffected = @@ROWCOUNT` before the commit — Blorgbeard, Aug 04 '13 at 10:42
@Blorgbeard, Yes It is working. Thanks. But why it is not working after `COMMIT`? any idea? — Billa, Aug 04 '13 at 10:46
@Billa Apparently because `@@rowcount` is supposed to return number of rows affected by the last statement, and `commit` is a statement that is [documented](http://msdn.microsoft.com/en-us/library/ms187316.aspx) to set `@@rowcount` to 0. — GSerg, Aug 04 '13 at 11:11

score 4 · Answer 1 · edited May 23 '17 at 12:05

4

You need to check @@ROWCOUNT before you commit.

As @GSerg noted, this is because @@rowcount is supposed to return number of rows affected by the last statement, and commit is a statement that is documented to set @@rowcount to 0.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 06 '14 at 20:02

Blorgbeard

101,031
48
228
272

Getting No of records inserted in Transaction

1 Answers1