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

User · Answer

WARNING    ROWCOUNT may return bogus data if the table being altered has triggers attached to it   The   ROWCOUNT will return the number of records affected by the TRIGGER  not the actual statement

User · Answer

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

User · Answer

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

User · Answer

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   returns -1 when doing Insert   Update   Delete

User · Answer

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

User · Answer

ROWCOUNT

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

Examples related to sql

Examples related to sql-server

Examples related to oracle

Examples related to tsql

Examples related to plsql