locked
Is possible for @@rowcount to be zero and @@error != 0 ? RRS feed

  • Question

  • hi friends,

    Assume there is an update statement that updates a table.

    if i consider @@error and @@rowcount keywords.

    could there be a situation where @@rowcount = 0 and @@error != 0? if so please explain when it could probably  happen?

    thanks

    Tuesday, June 15, 2010 12:23 PM

Answers

  • Here is an example.

    USE tempdb;
    GO
    DECLARE @T TABLE (c1 int NOT NULL PRIMARY KEY);
    
    INSERT INTO @T(c1) VALUES(1);
    INSERT INTO @T(c1) VALUES(2);
    
    BEGIN TRY
     UPDATE @T
     SET c1 = 1
     WHERE c1 = 2;
    END TRY
    BEGIN CATCH
     SELECT
      @@ROWCOUNT AS [RowCount], 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_PROCEDURE() AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    GO

    The update statement will succeed or fail as a whole.

    AMB

    • Marked as answer by KJian_ Monday, June 21, 2010 1:29 PM
    Tuesday, June 15, 2010 1:41 PM

All replies

  • Here is an example.

    USE tempdb;
    GO
    DECLARE @T TABLE (c1 int NOT NULL PRIMARY KEY);
    
    INSERT INTO @T(c1) VALUES(1);
    INSERT INTO @T(c1) VALUES(2);
    
    BEGIN TRY
     UPDATE @T
     SET c1 = 1
     WHERE c1 = 2;
    END TRY
    BEGIN CATCH
     SELECT
      @@ROWCOUNT AS [RowCount], 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() AS ErrorState,
      ERROR_PROCEDURE() AS ErrorProcedure,
      ERROR_LINE() AS ErrorLine,
      ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    GO

    The update statement will succeed or fail as a whole.

    AMB

    • Marked as answer by KJian_ Monday, June 21, 2010 1:29 PM
    Tuesday, June 15, 2010 1:41 PM
  • A couple of words about your question itself. The answer is YES. Here is the example:

    SELECT 1 / 0
    SELECT @@error, @@rowcount

    For the majority of cases if @@error != 0 then @@rowcount = 0 as SQL Server consider each command as an indivisible part of work: either the whole work is done or the whole work is failed.

    Otherwise, it's a common situation when @@error = 0 but @@rowcount = 0 too. See example below:

    CREATE TABLE #MyTable ( MyColumn VARCHAR(20) NULL )

    UPDATE #MyTable
    SET MyColumn = 'my value'
    WHERE MyColumn = 'current value'

    As you see, #MyTable has no values before updating. But the command is correct.

    In addition I would recommend you to not use TRY/CATCH statements within such statements as UPDATE. Usually table updates occur within explicit transaction. Even if you're creating your own stored procedure, you can't be sure it will never be invoked inside a transaction. The problem is that transaction becomes uncommitable if some error occurs inside your TRY statement (even if it's successfully processed by CATCH statement). It means that despite you're considering some errors as inessential, SQL Server will not let commit that transaction, only ROLLBACK will be appropriate. And it will be hard to find the reason of this for the third person. See details in TRY...CATCH (Transact-SQL) article, section "Uncommitable Transactions ans XACT_STATE".


    • Edited by Ivan Hreshylo Friday, May 31, 2013 1:15 PM grammatical mistake
    Friday, May 31, 2013 1:14 PM