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

Question

For INSERT , UPDATE and DELETE SQL statements executed directly against the database, most database providers return the number of rows affected. For stored procedures, the number of affected records is always -1 .

How do we get the number of records that are affected by the stored procedure?

Answer 1

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

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

Answer 2

@@RowCount will provide you with the number of records affected by the SQL query.

@@RowCount only works with immediate release. Therefore, if you are trap errors, you need to do this on one line. If you split it, you will skip the one you put second.

 SELECT @NumRowsChanged = @@ROWCOUNT, @ErrorCode = @@ERROR 

If you have multiple statements, you will need to record the number of lines affected for each and add them.

 SELECT @NumRowsChanged = @NumRowsChanged + @@ROWCOUNT, @ErrorCode = @@ERROR 

Answer 3

It turns out that SET NOCOUNT ON was set in the script stored procedure (by default in SQL Server Management Studio), and SqlCommand.ExecuteNonQuery(); always returned -1.

I just disabled: SET NOCOUNT OFF without using @@ROWCOUNT .

More details here: SqlCommand.ExecuteNonQuery () returns -1 on insert / update / delete

Answer 4

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.

Answer 5

@@ ROWCOUNT

Answer 6

WARNING: @@ROWCOUNT can return fake data if the modified table has triggers attached to it!

@@ROWCOUNT will return the number of records affected by TRIGGER, not the actual statement!