TSQL 中 while 循环中的 continue 语句？

发布于 2024-09-24 23:22:00 字数 348 浏览 7 评论 0原文

在 Sql Server 2000 和 2005 中，我在 while 循环中运行 select 语句。 JFYI，这个 select 语句正在连接到许多链接服务器并获取一些值。

如果有任何错误，我希望它仍然应该执行循环中的下一条语句（类似于 c# 中的 continue 语句）

示例：-

while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'
 exec sp_executesql @sql
 set @rowcount = @rowcount +1
End

原文

In Sql Server 2000 and 2005 I am running a select statement in a while loop. JFYI, this select statement is connecting to many linked servers and getting some values.

IF there is any error, i want it should still execute next statement in the loop (similar to continue statement in c#)

Example:-

while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'
 exec sp_executesql @sql
 set @rowcount = @rowcount +1
End

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝色星空 2024-10-01 23:22:00

从这里开始： http://www.sommarskog.se/error_handling_2005.html

请记住，有些错误是会话甚至批处理终止符，您无法捕获这些错误

我给您的链接（以及该页面上的 2 个链接）应该为您提供有关如何处理此

顺便说一句的足够信息，除非它是不可捕获的错误，否则它将继续执行

run this

declare @rowcount int, @sql nvarchar(100)
set @rowcount = 1
while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'
 exec sp_executesql @sql
 print @rowcount
 set @rowcount = @rowcount +1
End

这是输出

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
1
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
2
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
4
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
5
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
6
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
7
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
8
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
9

这是如何使用 TRY CATCH 来捕获它

declare @rowcount int, @sql nvarchar(100)
set @rowcount = 1
while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'

 begin try 
      exec sp_executesql @sql
 end try 
 begin catch
      select ERROR_MESSAGE() -- or do something
 end catch
 print @rowcount
 set @rowcount = @rowcount +1
End

Start here: http://www.sommarskog.se/error_handling_2005.html

Keep in mind that some errors are session and even batch terminators and you can't trap those

The link I gave you(and the 2 links on that page) should give you enough information on how to handle this

BTW, unless it is a non trapable error it will continue executing

run this

declare @rowcount int, @sql nvarchar(100)
set @rowcount = 1
while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'
 exec sp_executesql @sql
 print @rowcount
 set @rowcount = @rowcount +1
End

Here is the output

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
1
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
2
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
4
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
5
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
6
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
7
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
8
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
9

Here is how you can use TRY CATCH to trap this

declare @rowcount int, @sql nvarchar(100)
set @rowcount = 1
while @rowcount < 10
begin
 set @sql = 'select * from <Remotemachine>.db1.dbo.table1'

 begin try 
      exec sp_executesql @sql
 end try 
 begin catch
      select ERROR_MESSAGE() -- or do something
 end catch
 print @rowcount
 set @rowcount = @rowcount +1
End

回复收藏 0 原文