Geek Answers Handbook

Choose number of deleted records using t-SQL

I cannot figure out how to select the number of previously deleted records with SQL Server 2008. Is this something like this?

DELETE FROM [table] WHERE [id]=10 SELECT SCOPE_IDENTITY()
+4
source share
3 answers

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

@@ROWCOUNT

Returns the number of rows affected by the last statement.

Notes

...

Data Language Operators (DML) set @@ROWCOUNT to the number of rows affected by the request and return this value to the client. DML statements cannot send any lines to the client.

Note that I say "right after" because other operators can change the value of @@ROWCOUNT , even if they do not affect the lines as such:

DECLARE CURSOR and FETCH set @@ROWCOUNT to 1.

...

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

+12
source

You can also SET NOCOUNT OFF .

Note

When SET NOCOUNT is turned on, the count (indicating the number of rows referenced by the Transact-SQL statement) is not returned. When SET NOCOUNT is off, the counter returns.

+2
source

When debugging a stored procedure, I usually use this piece of code 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 the operation, for example, to delete. I will put a number in the message to track which fragment I use in the code. This is very useful when you are dealing with a large stored procedure with a lot of lines of code.

0
source

More articles:


All Articles