SQL Server : ExecuteNonQuery shows incorrect rows affected.

For one of client as per functionality we were invoking a SQL Server procedure using ExecuteNonQuery method.
Within procedure we had a DML statement performing update and on rows affected as return from ExecuteNonQuery performing further functional stuff.

In one of case for a table, though Rows impacted were only 1 but rows affected return through ExecuteNonQuery was more than 1.
On further analysis, identified we had a trigger enabled on updated table performing some DML actions.

From MSDN documentation:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1

Let understand it better with field work :
We will create a simple procedure with an insert on table having trigger enabled.

drop table tab_row_affected;
create table tab_row_affected
(col1 int ,col2 datetime ,col3 varchar(10));

drop table tab_row_affected1;
create table tab_row_affected1
(col1 int , col2 datetime ,col3 varchar(10));

--Create procedure with insert on tab_row_affected
CREATE PROCEDURE USP_TESTROWSAFFECTED
AS
INSERT INTO tab_row_affected VALUES (1,GETDATE(),'INSERT');
PRINT @@ROWCOUNT

---create trigger on tab_row_affected
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Sample C# Code :

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnection myConn = new SqlConnection(@"");
try
{
SqlCommand scommand = new SqlCommand("dbo.USP_TESTROWSAFFECTED", myConn);
scommand.CommandType = System.Data.CommandType.StoredProcedure;
myConn.Open();
Console.WriteLine("Calling Stored Procedure!!");
int rows_affected = scommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected :: " + rows_affected.ToString());
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConn.Close();
}}}}

On Executing Above Code, below is the Console output :

As solution to get only Rows affected within procedure and ignore rows affected as part of trigger we can use”SET NOCOUNT ON” in Trigger.
When NOCOUNT is set as ON for a block, it stop sending Count of rows affected.

/*Changing Trigger with NOCOUNT*/
SET NOCOUNT ON, will
CREATE TRIGGER TRIG_TESTROWSAFFECTED on tab_row_affected
FOR INSERT
AS
SET NOCOUNT ON;
INSERT INTO tab_row_affected1 VALUES (1,GETDATE(),'TRIGGER');

Post Trigger changes, now rows affected is reflecting only rows impacted within procedure.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s