CODEWITHSUNDEEP
sqlsql-servert-sql
user5115180
user5115180

Reputation:

Stored Procedure doesn't return affected rows after exception handling

I have added exception handling in my stored procedure as below.

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;

 BEGIN TRY
         BEGIN TRAN
                    UPDATE bud
                    SET bud.BudgetStateID    = @BudgetStateID
                    FROM [dbo].[BudgetOffice] bud
                        INNER JOIN [dbo].[vw_Office] vw
                            ON (vw.OfficeID   = bud.OfficeID)
                        WHERE   vw.DistrictID = @DistrictID
         IF @@ERROR = 0
      BEGIN   
          COMMIT TRAN;
          SELECT @@ROWCOUNT AS AffectedRow;
      END
 END TRY
 BEGIN CATCH
           SELECT @@ERROR AS ERROR
 ROLLBACK TRAN;
 END CATCH

 SET NOCOUNT OFF ;

 END

I need to return the number of affected rows using @@ROWCOUNT. But this stored procedure always returns rowcount as 0. Any reason for this. Do I need to write the @@rowcount statement right after update?

Upvotes: 1

Views: 1117

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

You need to store result of global variables in local variable because it will change after next instruction like:

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;
 DECLARE @rowcount INT, @error INT;

 BEGIN TRY
         BEGIN TRAN
                    UPDATE bud
                    SET bud.BudgetStateID = @BudgetStateID
                    FROM [dbo].[BudgetOffice] bud
                    JOIN [dbo].[vw_Office] vw
                      ON vw.OfficeID   = bud.OfficeID
                    WHERE vw.DistrictID = @DistrictID;

      SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;

      IF @error = 0
      BEGIN   
          COMMIT TRAN;
          SELECT @rowcount AS AffectedRow;
      END

 END TRY
 BEGIN CATCH
     SELECT @@ERROR AS ERROR
     ROLLBACK TRAN;
 END CATCH
 END

Or even better resign for using @@ERROR in TRY CATCH block:

ALTER PROCEDURE [dbo].[BUDGETUPDATE] 
 @DistrictID        int
AS
BEGIN
 SET NOCOUNT ON ;

 BEGIN TRY
    BEGIN TRAN
           UPDATE bud
           SET bud.BudgetStateID = @BudgetStateID
           FROM [dbo].[BudgetOffice] bud
           JOIN [dbo].[vw_Office] vw
             ON vw.OfficeID   = bud.OfficeID
           WHERE vw.DistrictID = @DistrictID;

           SELECT @@ROWCOUNT AS AffectedRow;

    COMMIT TRAN; 
 END TRY
 BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ERROR
    ROLLBACK TRAN;
 END CATCH
 END

And where is @BudgetStateID defined?

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need to select @@ROWCOUNT after your UPDATE statement. As per the documentation:

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Since your @@ROWCOUNT is after the COMMIT TRAN, @@ROWCOUNT returns 0.

Upvotes: 3

Related Questions