返回受 UPDATE 语句影响的行数 [英] Return number of rows affected by UPDATE statements

问题描述

如何获取存储过程 (SQL Server 2005) 中受 UPDATE 查询影响的行数作为结果集.例如

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

然后返回:

Table1    Table2    Table3    Table4
32        45        0         3

推荐答案

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

这篇关于返回受 UPDATE 语句影响的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！