Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 345
dthrasher Avatar asked Jul 29 '09 16:07

dthrasher


People also ask

How do you get the number of rows affected by a query?

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.

How can use stored procedure Rowcount in SQL Server?

SQL Server @@ROWCOUNT with Try Catch@@ROWCOUNT returns the affected rows from any statement, even if it's not DML or a SELECT query.


1 Answers

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.

like image 110
OMG Ponies Avatar answered Sep 23 '22 01:09

OMG Ponies