Return number of rows affected by UPDATE statements

Question

How can I get the number of rows affected by an UPDATE query in a Stored Procedure (SQL Server 2005), as a resultset. e.g.

CREATE PROCEDURE UpdateTables AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      UPDATE Table1 Set Column = 0 WHERE Column IS NULL     UPDATE Table2 Set Column = 0 WHERE Column IS NULL     UPDATE Table3 Set Column = 0 WHERE Column IS NULL     UPDATE Table4 Set Column = 0 WHERE Column IS NULL END 

Then return:

Table1    Table2    Table3    Table4 32        45        0         3 

Answer 1

CREATE PROCEDURE UpdateTables AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     DECLARE @RowCount1 INTEGER     DECLARE @RowCount2 INTEGER     DECLARE @RowCount3 INTEGER     DECLARE @RowCount4 INTEGER      UPDATE Table1 Set Column = 0 WHERE Column IS NULL     SELECT @RowCount1 = @@ROWCOUNT     UPDATE Table2 Set Column = 0 WHERE Column IS NULL     SELECT @RowCount2 = @@ROWCOUNT     UPDATE Table3 Set Column = 0 WHERE Column IS NULL     SELECT @RowCount3 = @@ROWCOUNT     UPDATE Table4 Set Column = 0 WHERE Column IS NULL     SELECT @RowCount4 = @@ROWCOUNT      SELECT @RowCount1 AS Table1, @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4 END