How can I get the number of records affected by a stored procedure?

Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle.

Mind that if you have multiple INSERT/UPDATE/DELETE, you'll need a variable to store the result from @@ROWCOUNT for each operation.

@@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

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