locked
@@rowcount is 0 RRS feed

  • Question

  • Hi,

    In the SP, I'm checking these.

      begin transaction
       delete from ...;

       insert into ...
       where header_id=@header_id
       and ...;
       
      commit transaction;

      if @@rowcount=0
      begin
       set @message=...;
       ...
      end

    I don't know why now @@rowcount is returning 0. But actually there is one 1 row inserted if I directly run the above 2 statements in Management studio using the same Header ID.


    Many Thanks & Best Regards, HuaMin Chen
    Monday, March 7, 2011 6:49 AM

Answers

  • Hi,

    Check your rowcount inside the transaction.

    Have a look at

    http://msdn.microsoft.com/en-us/library/ms187316.aspx

     

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

     


    Thanks and regards, Rishabh
    Monday, March 7, 2011 7:03 AM

All replies

  • Hi,

    Check your rowcount inside the transaction.

    Have a look at

    http://msdn.microsoft.com/en-us/library/ms187316.aspx

     

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

     


    Thanks and regards, Rishabh
    Monday, March 7, 2011 7:03 AM
  • You need to check @@rowcount right after the statement.  You can also save it to a variable for later use.

    DECLARE @Rowcount1 int
    UPDATE AdventureWorks2008.Production.Product SET ModifiedDate=ModifiedDate
    SET @Rowcount1 = @@ROWCOUNT
    SELECT @Rowcount1
    -- 504
    

    Kalman Toth, SQL Server & BI Training; SQLUSA.com
    Monday, March 7, 2011 7:12 AM
  • Every new statement starts a new rowcount. For Example:

    select * from table
    Select @@rowcount  -- brings the correct number of rows

    select * from table
    select @@error
    Select @@rowcount  -- brings 1, namly the result of select @@error

    Monday, March 7, 2011 7:16 AM