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

sql sql-server oracle tsql plsql
dthrasher picture dthrasher · Jul 29, 2009 · Viewed 155.5k times · Source

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?

Answer

OMG Ponies picture OMG Ponies · Jul 29, 2009

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.

Related questions

Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
What's the SQL national character (NCHAR) datatype really for?

As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings: In SQL Server, NCHAR is …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More