blocks|key|3243482|text|只需创建一个变量来保存ROWCOUNT，然后您就可以做您想做的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3243483|DECLARE+@RCount+int

INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=x2
SET+@RCount+=+@@ROWCOUNT
IF+@RCount+=+0
BEGIN+
++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2
+++SET+@RCount+=+@@ROWCOUNT
END
IF+@RCount+=+0
BEGIN
++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=z1+and+param2=x2
+++SET+@RCount+=+@@ROWCOUNTE
END
....|code-block|syntax|javascript|3243484|本例中的另一个可用选项是将Where条件添加到插入中。|offset|length|style|CODE|3243485|INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=x2
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2+
++++++++++++++++++WHERE+NOT+EXISTS+(SELECT+*+FROM+temptable+)
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2+
++++++++++++++++++WHERE+NOT+EXISTS+(SELECT+*+FROM+temptable+)
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2+
++++++++++++++++++WHERE+NOT+EXISTS+(SELECT+*+FROM+temptable+)|3243486|entityMap^0|0|0|D|5|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Just create a variable to hold the ROWCOUNT and then you can do what you want 
e.g.

<pre><code>DECLARE @RCount int

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
SET @RCount = @@ROWCOUNT
IF @RCount = 0
BEGIN 
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
 SET @RCount = @@ROWCOUNT
END
IF @RCount = 0
BEGIN
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
 SET @RCount = @@ROWCOUNTE
END
....
</code></pre>

Another option available in this case is to add a <code>Where</code> condition to your inserts

<pre><code>INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
 WHERE NOT EXISTS (SELECT * FROM temptable )
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
 WHERE NOT EXISTS (SELECT * FROM temptable )
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2 
 WHERE NOT EXISTS (SELECT * FROM temptable )
</code></pre>

blocks|key|3243522|text|每个执行的语句都会重置@@ROWCOUNT，其中包括一个IF语句；这就是为什么当您关心它时，通常做的第一件事就是将它捕获到一个变量中，以便您以后可以使用该值来完成一些事情，而不必担心它已经改变了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3243523|UPDATE+(无耻的自我推销，真的)：如果您想看到您的SQL自动拆分成语句(因此您将看到每个IF/INSERT组合构成一个语句)，您可以通过类似于我的PoorSQL.com站点的SQL格式化程序传递它--它将在语句之间放置空行！：|3243524|另外:关于如何处理此问题的快速概述，这也适用于@@ERROR：http://www.novicksoftware.com/TipsAndTricks/sql-error-reset-after-every-statement.htm+(尽管在SQL+2005及以后版本中，您应该使用try/catch而不是@@ERROR)。|3243525|entityMap|0|LINK|mutability|MUTABLE|url|http://poorsql.com/|1|http://www.novicksoftware.com/TipsAndTricks/sql-error-reset-after-every-statement.htm^0|B|A|0|24|B|0|0|N|7|49|7|V|2D|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|H|3|I|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|J|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]]]

the <code>@@ROWCOUNT</code> gets reset with every statement that executes, and that includes an IF statement; that's why the FIRST thing you typically do, when you care about it, is capture it into a variable so that you can do things with the value afterwards without worrying about it having changed. 

UPDATE (shameless self-promotion, really): If you want to see your SQL split out into Statements automatically (so here you would see that each IF/INSERT combination constitutes one statement), you could pass it through a SQL Formatter like my <a href="http://poorsql.com" rel="nofollow">PoorSQL.com</a> site - it will put an empty line between the statements! :)

Also: nice quick overview of how to deal with this issue, which also applies to <code>@@ERROR</code>: <a href="http://www.novicksoftware.com/TipsAndTricks/sql-error-reset-after-every-statement.htm" rel="nofollow">http://www.novicksoftware.com/TipsAndTricks/sql-error-reset-after-every-statement.htm</a> (although in SQL 2005 and later you should really be using try/catch rather than <code>@@ERROR</code>)

blocks|key|1689852|text|因为@@rowcount只能检查一次，所以您的脚本将跳过一个插入，然后执行下一个插入。您必须正确地嵌套IFs：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1689853|创建#试探性|1689854|INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=x2
IF+@@ROWCOUNT+=+0
begin
+++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2
+++IF+@@ROWCOUNT+=+0
+++begin
+++++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=z1+and+param2=x2
+++++IF+@@ROWCOUNT+=+0
+++++begin
++++++++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=y2
+++++++IF+@@ROWCOUNT+=+0
+++++++begin
++++++++++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=y2
++++++++++IF+@@ROWCOUNT+=+0
+++++++++++++INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=z1+and+param2=y2
+++++++end
+++++end
+++end
end|code-block|syntax|javascript|1689855|有一些方法可以通过使用循环并在相反的条件下中断(<>0)来使这更优雅(避免深层嵌套)：|offset|length|style|CODE|1689856|create+#temptable

while+(1=1)
begin
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=x2
IF+@@ROWCOUNT+<>+0
+++break
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=x2
IF+@@ROWCOUNT+<>+0
+++break
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=z1+and+param2=x2
IF+@@ROWCOUNT+<>+0
+++break
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=x1+and+param2=y2
IF+@@ROWCOUNT+<>+0
+++break
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=y1+and+param2=y2
IF+@@ROWCOUNT+<>+0
+++break
INSERT+INTO+#temptable+(...)+SELECT+a,b..,n+FROM+TABLE1+where+param1=z1+and+param2=y2
break
end|1689857|entityMap^0|0|0|0|O|3|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Because the @@rowcount can only be checked once, your script will skip one insert but then execute the next one. You must properly nest the IFs: 

create #temptable

<pre><code>INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT = 0
begin
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
 IF @@ROWCOUNT = 0
 begin
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
 IF @@ROWCOUNT = 0
 begin
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
 IF @@ROWCOUNT = 0
 begin
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
 IF @@ROWCOUNT = 0
 INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2
 end
 end
 end
end
</code></pre>

There are ways to make this more elegant (avoid the deeep nesting) by using a loop and break on the opposite condition (<code>&lt;&gt;0</code>):

<pre><code>create #temptable

while (1=1)
begin
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT &lt;&gt; 0
 break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
IF @@ROWCOUNT &lt;&gt; 0
 break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
IF @@ROWCOUNT &lt;&gt; 0
 break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
IF @@ROWCOUNT &lt;&gt; 0
 break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
IF @@ROWCOUNT &lt;&gt; 0
 break
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2
break
end
</code></pre>

blocks|key|2887032|text|我最近遇到了这种情况，这是由“化妆品的原因”引起的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2887033|INSERT+INTO+asd
VALUES+a,+s,+d

IF+@@rowcount+>+0
...|code-block|syntax|javascript|2887034|...and...|2887035|INSERT+INTO+asd
VALUES+a,+s,+d
IF+@@rowcount+>+0
...|2887036|都不算相等。由于某种原因，行之间的额外空间(看起来更好)重置@@rowcount。|2887037|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I had this happen to me recently, and it was caused by &quot;cosmetic reasons&quot;.
<pre><code>INSERT INTO asd
VALUES a, s, d

IF @@rowcount &gt; 0
...
</code></pre>
...and...
<pre><code>INSERT INTO asd
VALUES a, s, d
IF @@rowcount &gt; 0
...
</code></pre>
Are not counted as equal to each other. The extra space (looks better) between the lines resets @@rowcount for some reason.

I am running a set of iterative SQL queries based on parameters that I determine ahead of time. Each set of queries in this .sql script are executed and insert a row into a temp table. What I am trying to do is to stop the execution of the rest of the statements once I have inserted one row into the temp table. To do this I am checking the @@ROWCOUNT value after each INSERT. If it is 0 then I continue to the next INSERT statement. If it is &lt;> 0 then I want to stop. I am seeing some interesting effects and I am wondering if @@ROWCOUNT is altered when I check it.
Here is a basic example:

<pre><code>create #temptable

INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=x2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=x1 and param2=y2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=y1 and param2=y2
IF @@ROWCOUNT = 0
INSERT INTO #temptable (...) SELECT a,b..,n FROM TABLE1 where param1=z1 and param2=y2
</code></pre>

What I am seeing is that when the 1st INSERT has rows it does not execute the 2nd INSERT but it does execute the 3rd. It then does not execute the 4th INSERT but it does execute the 5th, etc etc. Shouldn't it stop all further execution or is there some scope that is being changed?

Is there a way to make this SQL stop once an INSERT statement has INSERTED a row into the temp table?

@@ROWCOUNT Not Working Like I Think?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在根据提前确定的参数运行一组迭代SQL查询。这个.sql脚本中的每一组查询都被执行，并将一行插入到临时表中。我要做的是，一旦将一行插入temp表中，就会停止其余语句的执行。为此，我在每次插入后检查@@ROWCOUNT值。如果为0，则继续下一个INSERT语句。如果它是<> 0，那么我想停止。我看到了一些有趣的效果，...

问@@ROWCOUNT不像我想的那样工作？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问@@ROWCOUNT不像我想的那样工作？EN