I'm using the following SQL statements block from an ASP.NET C# program:
SET XACT_ABORT ON; --Need to roll back upon failure
BEGIN TRANSACTION; --Need to be performed atomically
UPDATE MyTable SET Col1='SomeValue' WHERE ColID='N';
DELETE FROM MyTable WHERE ColID='X';
COMMIT;
SET XACT_ABORT OFF;
The SQL block above is executed at once by calling SqlCommand.ExecuteNonQuery Method that should return:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.
But what I get upon return from ExecuteNonQuery() is the number of updated records added to the number of deleted ones. So my question is, can I somehow make it return only the number of deleted records?
This one line of code SET NOCOUNT ON, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.
By removing this extra overhead from the network it can greatly improve overall performance for your database and application.
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.
SET NOCOUNT ON
SET XACT_ABORT ON; --Need to roll back upon failure
BEGIN TRANSACTION; --Need to be performed atomically
UPDATE MyTable SET Col1='SomeValue' WHERE ColID='N';
SET NOCOUNT OFF
DELETE FROM MyTable WHERE ColID='X';
COMMIT;
SET XACT_ABORT OFF;