blocks|key|1508176|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1508177|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1293623|text|您可能需要在执行过程中收集统计数据，但@@ROWCOUNT捕获了以下内容：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1293624|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|code-block|syntax|javascript|1293625|entityMap^0|J|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You might need to collect the stats as you go, but <code>@@ROWCOUNT</code> captures this:

<pre><code>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
</code></pre>

blocks|key|1293700|text|这正是SQL+Server2005以后版本中的OUTPUT子句的出色之处。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1293701|示例|1293702|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'|code-block|syntax|javascript|1293703|结果将返回以下内容|1293704|++++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|1293705|在您的特定情况下，由于不能将聚合函数与OUTPUT一起使用，因此需要在表变量或临时表中捕获INSERTED.*的输出，并对记录进行计数。例如,|1293706|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|1293707|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms177564.aspx^0|N|6|N|6|0|0|0|0|0|0|J|6|19|A|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|F|3|G|5|6|7|18|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|19|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1A|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1B|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|S|3|T|5|J|7|1H|8|@]|D|@]|E|$K|L]]|$1|U|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

This is exactly what the <a href="http://technet.microsoft.com/en-us/library/ms177564.aspx" rel="noreferrer"><code>OUTPUT</code></a> clause in SQL Server 2005 onwards is excellent for.

EXAMPLE

<pre><code>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 &gt; '2009 JUL 09'
</code></pre>

Results in the following being returned

<pre><code> 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
</code></pre>

In your particular case, since you cannot use aggregate functions with <code>OUTPUT</code>, you need to capture the output of <code>INSERTED.*</code> in a table variable or temporary table and count the records. For example,

<pre><code>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 &gt; '2009 JUL 09'


-- now get the count of affected records
SELECT COUNT(*) FROM @temp
</code></pre>

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.

<pre><code>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
</code></pre>

Then return:

<pre><code>Table1 Table2 Table3 Table4
32 45 0 3
</code></pre>

Return number of rows affected by UPDATE statements

SQL Server

如何获取存储过程(SQL Server2005)中受UPDATE查询影响的行数作为结果集。例如：CREATE PROCEDURE UpdateTablesASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfer...

问Return受UPDATE语句影响的行数
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Return受UPDATE语句影响的行数EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Return受UPDATE语句影响的行数
EN