Geek asks and answers

Disallow record counting in part of SQL statement for SQL Server

I use the following block of SQL statements 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 immediately by calling the SqlCommand.ExecuteNonQuery method , which should return:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

But what I get from returning from ExecuteNonQuery () is the number of updated records added to the number of deleted ones. So my question is: can I somehow return only the number of deleted records to it?

+3
source share
2 answers

SET NOCOUNT ON, , , SQL Server T-SQL. SELECT, INSERT, UPDATE DELETE.

, .

, T-SQL, @@ROWCOUNT. SET NOCOUNT, (@@ROWCOUNT) , , .

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;
+3

, . , , ROWCOUNT_BIG(), , :

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';
SELECT ROWCOUNT_BIG();   --resets rowcount
DELETE FROM MyTable WHERE ColID='X';
SELECT ROWCOUNT_BIG();   --retrieves the needed row count
COMMIT;
SET XACT_ABORT OFF;
0

More articles:


All Articles