Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select number of deleted records with t-SQL

I can't seem to figure out how to select the number of previously deleted records with SQL Server 2008. Is it something like this?

DELETE FROM [table] WHERE [id]=10
SELECT SCOPE_IDENTITY()
like image 530
c00000fd Avatar asked Jan 13 '13 04:01

c00000fd


2 Answers

Use SELECT @@ROWCOUNT immediately after the DELETE statement. You can read more about @@ROWCOUNT on MSDN:

@@ROWCOUNT

Returns the number of rows affected by the last statement.

Remarks

...

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

Note that I say "immediately after" because other statements can change the value of @@ROWCOUNT, even if they don't affect rows, per se:

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

...

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

like image 96
ta.speot.is Avatar answered Oct 12 '22 16:10

ta.speot.is


You can also SET NOCOUNT OFF.

Remarks

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

like image 25
bonCodigo Avatar answered Oct 12 '22 16:10

bonCodigo