No value returned from SQL update?

Question

Possible Duplicate:
Getting no. of rows affected after running select query in SQL Server 2005

How can I get the number of rows updated by an update? Something like this:

select update container set a = 1

Please read about this article from Microsoft about `@@ROWCOUNT`: http://technet.microsoft.com/en-us/library/ms187316.aspx — RDSAGAR, Nov 16 '12 at 18:34

score 0 · Answer 1 · answered Nov 16 '12 at 18:31

You can use

SET NOCOUNT OFF;
GO

this will show a count of the number of rows that were updated.

You can also use the ever-so-cool @@ROWCOUNT variable which returns that number of rows affected by the last operation.

SELECT @@ROWCOUNT;  
GO

I hope this helps.

score 0 · Answer 2 · answered Nov 16 '12 at 18:32

DECLARE @UPDATED_RECORDS int
SET @UPDATED_RECORDS = 0

select update container set a = 1

Select @UPDATED_RECORDS = @@ROWCOUNT

Immediately following the update statement and you should get the total number of records updated now stored in the @UPDATED_RECORDS variable.

Of course, without a filter on the update, you are updating every row in the table, so in this case SELECT COUNT(1) FROM CONTAINER should provide the same result.

No value returned from SQL update?

2 Answers2