SQL Server:如何中止查询分析器中的一系列批处理? [英] SQL Server: How to abort a series of batches in Query Analyzer?

问题描述

我有一系列由特殊的查询分析器批处理分隔符关键字分隔的 T-SQL 语句:

i have a series of T-SQL statements separated by the special Query Analyzer batch separator keyword:

GO

如果一个批次失败，我需要查询分析器不要尝试后续批次 - 我希望它停止处理一系列批次.

If one batch fails, i need Query Analyzer to not try subsequent batches - i want it to stop processing the series of batches.

例如:

PRINT 'This runs'
go

SELECT 0/0, 'This causes an error'
go

PRINT 'This should not run'
go

输出:

This runs
Server: Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
This should not run

可能吗?

实际使用中的一个例子可能是:

An example of this in real use might be:

 sp_rename 'Shelby', 'Kirsten'
 go

 DROP VIEW PeekAView
 go

 CREATE VIEW PeekAViewAS 
 SELECT * FROM Kirsten
 go

推荐答案

这是我的做法:

PRINT 'This runs'
go

SELECT 0/0, 'This causes an error'
go
if (@@error <> 0)
    Begin
    set nocount on
    set noexec on
    End
GO

PRINT 'This should not run'
go

set noexec off
set nocount off
GO

noexec"模式将 SSMS 置于一种状态，它只编译 T-SQL 而实际上并不执行它.这类似于不小心按下了解析工具栏按钮 (Ctrl+F5) 而不是执行 (F5).

The "noexec" mode puts SSMS is a state where it just compiles the T-SQL and doesn't actually execute it. It is similar to accidentally pressing the Parse toolbar button (Ctrl+F5) instead of Execute (F5).

不要忘记在脚本结束时关闭 noexec.否则用户会被永久的命令成功完成"弄糊涂了.消息.

Don't forget to turn noexec back off at the end of your script. Otherwise users are going to get confused by permanent "Command(s) completed successfully." messages.

我在后续批处理中使用对@@error 的检查，而不是使用 TRY CATCH 块.在下一批中使用@@error 将捕获编译错误，例如表不存在".

I use the check against @@error in the subsequent batch instead of using TRY CATCH blocks. Using @@error in the next batch will catch compile errors, like "table doesn't exist".

除了 noexec 模式，我还切换了 nocount 模式.在 noexec 模式打开和 nocount 关闭的情况下，您的查询仍将报告一条消息(0 行受影响)".该消息始终报告零行，因为您处于 noexec 模式.但是，打开 nocount 会抑制这些消息.

In addition to the noexec mode, I also toggle the nocount mode. With noexec mode on and nocount off, your queries will still report a message "(0 rows(s) affected)". The message always reports zero rows, because you're in noexec mode. However, turning nocount on suppresses these messages.

另请注意，如果运行 SQL Server 2005，如果您正在跳过的命令引用不存在的表，并且该命令是批处理中的第一个命令，则它可能仍会给出错误消息.使用伪造的 Print 语句强制命令成为批处理中的第二个命令可以抑制这种情况.请参阅 MS 错误 #569263 了解更多详情.

Also note that if running SQL Server 2005 the command you are skipping might still give error messages if it references a table that doesn't exist and the command if the first command in the batch. Forcing the command to be the second command in the batch with a bogus Print statement can suppress this. See MS Bug #569263 for more details.

这篇关于SQL Server:如何中止查询分析器中的一系列批处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！