如何在 SQL Server 中组织无限 while 循环？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2456330/
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
How to organize infinite while loop in SQL Server?
提问by alpav
I want to use infinite WHILEloop in SQL Server 2005 and use BREAKkeyword to exit from it on certain condition.
我想WHILE在 SQL Server 2005 中使用无限循环并BREAK在特定条件下使用关键字退出它。
while truedoes not work, so I have to use while 1=1.
Is there a better way to organize infinite loop ?
while true不起作用，所以我必须使用while 1=1. 有没有更好的方法来组织无限循环？
I know that I can use goto, but while 1=1 begin ... endlooks better structurally.
我知道我可以使用goto，但在while 1=1 begin ... end结构上看起来更好。
回答by Daniel Vassallo
In addition to the WHILE 1 = 1as the other answers suggest, I often add a "timeout" to my SQL "infintie" loops, as in the following example:
除了WHILE 1 = 1其他答案所建议的之外，我经常在我的 SQL“infintie”循环中添加一个“超时”，如下例所示：
DECLARE @startTime datetime2(0) = GETDATE();
-- This will loop until BREAK is called, or until a timeout of 45 seconds.
WHILE (GETDATE() < DATEADD(SECOND, 45, @startTime))
BEGIN
-- Logic goes here: The loop can be broken with the BREAK command.
-- Throttle the loop for 2 seconds.
WAITFOR DELAY '00:00:02';
END
I found the above technique useful within a stored procedure that gets called from a long polling AJAXbackend. Having the loop on the database-side frees the application from having to constantly hit the database to check for fresh data.
我发现上述技术在从长轮询 AJAX后端调用的存储过程中很有用。在数据库端使用循环使应用程序不必不断访问数据库以检查新数据。
回答by Thomas
Using While 1 = 1with a Breakstatement is the way to do it. There is no constant in T-SQL for TRUEor FALSE.
使用While 1 = 1了Break语句来做到这一点。T-SQL 中没有TRUE或FALSE 的常量。
回答by Wolph
If you really have to use an infinite loop than using while 1=1is the way I'd do it.
如果您真的必须使用无限循环，while 1=1那么我会采用这种方式。
The question here is, isn't there some other way to avoid an infinite loop? These things just tend to go wrong ;)
这里的问题是，难道没有其他方法可以避免无限循环吗？这些事情往往会出错;)
回答by rio
you could use the snippet below to kick a sp after soem condition are rised. I assume that you ahev some sort of CurrentJobStatus table where all the jobs/sp keeps their status...
你可以使用下面的代码片段在 soem 条件上升后踢一个 sp。我假设您有某种 CurrentJobStatus 表，其中所有作业/sp 都保持其状态...
-- *** reload data on N Support.usp_OverrideMode with checks on Status
/* run
Support.usp_OverrideMode.Number1.sql
and
Support.usp_OverrideMode.Number2.sql
*/
DECLARE @FileNameSet TABLE (FileName VARCHAR(255));
INSERT INTO @FileNameSet
VALUES ('%SomeID1%');
INSERT INTO @FileNameSet
VALUES ('%SomeID2%');
DECLARE @BatchRunID INT;
DECLARE @CounterSuccess INT = 0;
DECLARE @CounterError INT = 0;
-- Loop
WHILE WHILE (@CounterError = 0 AND @CounterSuccess < (select COUNT(1) c from @FileNameSet) )
BEGIN
DECLARE @CurrenstStatus VARCHAR(255)
SELECT @CurrenstStatus = CAST(GETDATE() AS VARCHAR)
-- Logic goes here: The loop can be broken with the BREAK command.
SELECT @CounterSuccess = COUNT(1)
FROM dbo.CurrentJobStatus t
INNER JOIN @FileNameSet fns
ON (t.FileName LIKE fns.FileName)
WHERE LoadStatus = 'Completed Successfully'
SELECT @CounterError = COUNT(1)
FROM dbo.CurrentJobStatus t
INNER JOIN @FileNameSet fns
ON (t.FileName LIKE fns.FileName)
WHERE LoadStatus = 'Completed with Error(s)'
-- Throttle the loop for 3 seconds.
WAITFOR DELAY '00:00:03';
select @CurrenstStatus = @CurrenstStatus +char(9)+ '@CounterSuccess ' + CAST(@CounterSuccess AS VARCHAR(11))
+ char(9)+ 'CounterError ' + CAST(@CounterError AS VARCHAR(11))
RAISERROR (
'Looping... @ %s'
,0
,1
,@CurrenstStatus
)
WITH NOWAIT;
END
-- TODO add some codition on @CounterError value
/* run
Support.usp_OverrideMode.WhenAllSuceed.sql
*/
Note the code is flexibile you can add as many condition checks on the @FileNameSet table var Mario
请注意，代码是灵活的，您可以在 @FileNameSet 表 var Mario 上添加尽可能多的条件检查
