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

Solution 1

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.

Solution 2

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

Solution 3

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 Method#Remarks

When SET NOCOUNT ON is set on the connection (before or as part of executing the command, or as part of a trigger initiated by the execution of the command) the rows affected by individual statements stop contributing to the count of rows affected that is returned by this method.
If no statements are detected that contribute to the count, the return value is -1. If a rollback occurs, the return value is also -1.

Solution 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.

Solution 5

@@ROWCOUNT

View more solutions

Comments

[Tao]

Turns out this is not true: stackoverflow.com/questions/7005225/…

[Harvey Darvey]

This works for me. My stored proc is just a simple insert statements, and it seems to be working. thanks!

[Leo Gurdian]

Thanks! Good job on the reference.

[Deantwo]

The blogs.msdn.com link is dead.

[Dude Pascalou]

Thanks @Deantwo, I updated it (with a better one coming from msdn).