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?
MySQL ROW_COUNT() can be used to get the total number of rows affected by MySQL query. To illustrate it we are creating a procedure with the help of which we can insert records in a table and it will show us how many rows have been affected.
SQL Server @@ROWCOUNT with Try Catch@@ROWCOUNT returns the affected rows from any statement, even if it's not DML or a SELECT query.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With