Sql-server – SP returns positive RecordsAffected value even when TSQL statement rolled back

sql servert-sql

I'm attempting to build an all-or-nothing TSQL statement, something like this:

BEGIN TRANSACTION
BEGIN TRY
    -- Loop over a TVP passed as a param
    DECLARE i CURSOR LOCAL FAST_FORWARD FOR SELECT [stmt] FROM TVP param;
    OPEN i;
    FETCH NEXT FROM i INTO @iDB, @iTBL, @iATTRS, @iVALS;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @SQL = next row of TVP
            EXECUTE sys.sp_executesql @SQL
        END
    CLOSE i;
    DEALLOCATE i;
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH

The SP generally works as intended, in that the transactions are all or nothing, and running the SP from SSMS returns the error as selected in the catch statement.

But on the C# side, the dbResponse.RecordsAffected value is always incremented.

Why is this? Also, how can I get this SP to return the correct rows effected value? (i.e. the number of rows that were effected by the SP on success)

Is it possible to reset this value in the CATCH statement?

Best Answer

on the C# side, the dbResponse.RecordsAffected value is always incremented.

That's just a total of the row count messages returned from SQL Server. These are sent as queries are run, before the transaction has been committed or rolled back.

how can I get this SP to return the correct rows effected value?

Keep track of the row count in a variable, and return that to the client with an output parameter or a result set.

Is it possible to reset this value in the CATCH statement?

As @Kin suggested, you can SET NOCOUNT ON and no rowcount messages will be sent to the client. Then after you COMMIT you can turn NOCOUNT OFF and run a query to that generates the desired rowcount message. Perhaps something cheezy like:

set nocount on
--do stuff
declare @rows int = 25



set nocount off
select top (@rows) 1 a
into #bitbucket
from sys.objects o, sys.columns c

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

SQL Server – How Transactions Affect Each Other

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

SQL Server – How Transactions Affect Each Other

Related Question