CODEWITHSUNDEEP
sqlsql-serversql-server-2008
c00000fd
c00000fd

Reputation: 22265

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()

Upvotes: 5

Views: 13759

Answers (3)

James-Jesse Drinkard
James-Jesse Drinkard

Reputation: 15703

When debugging a stored procedure, I generally use this code snippet below:

DELCARE @Msg varchar(30)
...
SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
RAISERROR (@Msg, 0, 1) WITH NOWAIT

I use it before and after an operation, like for a delete. I'll put a number in the message to keep track of which snippet I'm on in the code. It's very helpful when you are dealing with a large stored procedure with lots of lines of code.

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

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.

Upvotes: 2

ta.speot.is
ta.speot.is

Reputation: 27214

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.

Upvotes: 16

Related Questions