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?
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.
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/DELETEs, you'll need a variable to store the result from @@ROWCOUNT for each operation.
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