SQL 返回受 UPDATE 语句影响的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1103260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return number of rows affected by UPDATE statements
提问by SamWM
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.
如何获取存储过程 (SQL Server 2005) 中受 UPDATE 查询影响的行数作为结果集。例如
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
回答by AdaTheDev
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
回答by Russ Cam
This is exactly what the OUTPUTclause in SQL Server 2005 onwards is excellent for.
这正是OUTPUTSQL Server 2005 及更高版本中的子句所擅长的。
EXAMPLE
例子
CREATE TABLE [dbo].[test_table](
[LockId] [int] IDENTITY(1,1) NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[LockId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 07','2009 JUL 07')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 08','2009 JUL 08')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 09','2009 JUL 09')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 10','2009 JUL 10')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 11','2009 JUL 11')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 12','2009 JUL 12')
INSERT INTO test_table(StartTime, EndTime)
VALUES('2009 JUL 13','2009 JUL 13')
UPDATE test_table
SET StartTime = '2011 JUL 01'
OUTPUT INSERTED.* -- INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed
WHERE
StartTime > '2009 JUL 09'
Results in the following being returned
结果返回以下结果
LockId StartTime EndTime
-------------------------------------------------------
4 2011-07-01 00:00:00.000 2009-07-10 00:00:00.000
5 2011-07-01 00:00:00.000 2009-07-11 00:00:00.000
6 2011-07-01 00:00:00.000 2009-07-12 00:00:00.000
7 2011-07-01 00:00:00.000 2009-07-13 00:00:00.000
In your particular case, since you cannot use aggregate functions with OUTPUT, you need to capture the output of INSERTED.*in a table variable or temporary table and count the records. For example,
在您的特定情况下，由于您不能将聚合函数与 一起使用OUTPUT，您需要捕获INSERTED.*表变量或临时表中的输出并计算记录数。例如，
DECLARE @temp TABLE (
[LockId] [int],
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL
)
UPDATE test_table
SET StartTime = '2011 JUL 01'
OUTPUT INSERTED.* INTO @temp
WHERE
StartTime > '2009 JUL 09'
-- now get the count of affected records
SELECT COUNT(*) FROM @temp
回答by butterchicken
You might need to collect the stats as you go, but @@ROWCOUNTcaptures this:
您可能需要随时收集统计数据，但要@@ROWCOUNT捕获以下内容：
declare @Fish table (
Name varchar(32)
)
insert into @Fish values ('Cod')
insert into @Fish values ('Salmon')
insert into @Fish values ('Butterfish')
update @Fish set Name = 'LurpackFish' where Name = 'Butterfish'
select @@ROWCOUNT --gives 1
update @Fish set Name = 'Dinner'
select @@ROWCOUNT -- gives 3
