For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1.
How do we get the number of records affected by a stored procedure?
This question is related to
sql
sql-server
oracle
tsql
plsql
@@RowCount will give you the number of records affected by a SQL Statement.
The @@RowCount works only if you issue it immediately afterwards. So if you are trapping errors, you have to do it on the same line. If you split it up, you will miss out on whichever one you put second.
SELECT @NumRowsChanged = @@ROWCOUNT, @ErrorCode = @@ERROR
If you have multiple statements, you will have to capture the number of rows affected for each one and add them up.
SELECT @NumRowsChanged = @NumRowsChanged + @@ROWCOUNT, @ErrorCode = @@ERROR
For Microsoft SQL Server you can return the @@ROWCOUNT variable to return the number of rows affected by the last statement in the stored procedure.
Turns out for me that SET NOCOUNT ON was set in the stored procedure script (by default on SQL Server Management Studio) and SqlCommand.ExecuteNonQuery(); always returned -1.
I just set it off: SET NOCOUNT OFF without needing to use @@ROWCOUNT.
More details found here : SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete
WARNING: @@ROWCOUNT may return bogus data if the table being altered has triggers attached to it!
The @@ROWCOUNT will return the number of records affected by the TRIGGER, not the actual statement!
Source: Stackoverflow.com