SQL Server 中的 T-SQL STOP 或 ABORT 命令

Question

提问by Phillip Senn

Is there a command in Microsoft SQL Server T-SQL to tell the script to stop processing? I have a script that I want to keep for archival purposes, but I don't want anyone to run it.

Microsoft SQL Server T-SQL 中是否有命令告诉脚本停止处理？我有一个脚本，我想保留用于存档目的，但我不希望任何人运行它。

Answer 1

采纳答案by Jed

An alternate solution could be to alter the flow of execution of your script by using the GOTOstatement...

另一种解决方案可能是通过使用GOTO语句来改变脚本的执行流程...

DECLARE  @RunScript bit;
SET @RunScript = 0;

IF @RunScript != 1
BEGIN
RAISERROR ('Raise Error does not stop processing, so we will call GOTO to skip over the script', 1, 1);
GOTO Skipper -- This will skip over the script and go to Skipper
END

PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';
PRINT 'This is where your working script can go';

Skipper: -- Don't do nuttin!

Warning! The above sample was derived from an example I got from Merrill Aldrich. Before you implement the GOTOstatement blindly, I recommend you read his tutorial on Flow control in T-SQL Scripts.

警告！上面的示例来自我从 Merrill Aldrich 获得的示例。在GOTO盲目地执行该语句之前，我建议您阅读他的T-SQL 脚本中的流量控制教程。

Answer 2

回答by boydc7

No, there isn't one - you have a couple of options:

不，没有 - 你有几个选择：

Wrap the whole script in a big if/end block that is simply ensured to not be true (i.e. "if 1=2 begin" - this will only work however if the script doesn't include any GO statements (as those indicate a new batch)
Use the return statement at the top (again, limited by the batch separators)
Use a connection based approach, which will ensure non-execution for the entire script (entire connection to be more accurate) - use something like a 'SET PARSEONLY ON'or 'SET NOEXEC ON'at the top of the script. This will ensure all statements in the connection (or until said set statement is turned off) will not execute and will instead be parsed/compiled only.
Use a comment block to comment out the entire script (i.e. /* and */)

将整个脚本包装在一个大的 if/end 块中，该块只是确保不为真（即“如果 1=2 开始”-但这仅在脚本不包含任何 GO 语句时才有效（因为那些表示新的批）
使用顶部的 return 语句（同样受批处理分隔符的限制）
使用基于连接的方法，这将确保整个脚本不执行（整个连接更准确） -在脚本顶部使用类似“SET PARSEONLY ON”或“SET NOEXEC ON”的内容。这将确保连接中的所有语句（或直到所述 set 语句被关闭）将不会执行，而只会被解析/编译。
使用注释块注释掉整个脚本（即 /* 和 */）

EDIT: Demonstration that the 'return' statement is batch specific - note that you will continue to see result-sets after the returns:

编辑：证明“返回”语句是特定于批处理的 - 请注意，返回后您将继续看到结果集：

select 1
return
go
select 2
return
select 3
go
select 4
return
select 5
select 6
go

Answer 3

回答by Sparky

Why not simply add the following to the beginning of the script

为什么不简单地将以下内容添加到脚本的开头

PRINT 'INACTIVE SCRIPT'
RETURN

Answer 4

回答by gbn

To work around the RETURN/GO issue you could put RAISERROR ('Oi! Stop!', 20, 1) WITH LOGat the top.

要解决 RETURN/GO 问题，您可以将其放在RAISERROR ('Oi! Stop!', 20, 1) WITH LOG顶部。

This will close the client connection as per RAISERROR on MSDN.

这将根据 MSDN 上的 RAISERROR关闭客户端连接。

The very bigdownside is you have to be sysadmin to use severity 20.

非常大的缺点是你必须的系统管理员使用严重性20。

Edit:

编辑：

A simple demonstration to counter Jersey Dude's comment...

一个简单的演示来反驳 Jersey Dude 的评论......

RAISERROR ('Oi! Stop!', 20, 1)  WITH LOG
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO

Answer 5

回答by Chaowlert Chaisrichalermpol

RAISERROR with severity 20 will report as error in Event Viewer.

严重性为 20 的 RAISERROR 将在事件查看器中报告为错误。

You can use SET PARSEONLY ON; (or NOEXEC). At the end of script use GO SET PARSEONLY OFF;

您可以使用 SET PARSEONLY ON; （或 NOEXEC）。在脚本结束时使用 GO SET PARSEONLY OFF;

SET PARSEONLY ON;
-- statement between here will not run

SELECT 'THIS WILL NOT EXEC';

GO
-- statement below here will run

SET PARSEONLY OFF;

Answer 6

回答by Magnus

Here is a somewhat kludgy way to do it that works with GO-batches, by using a "global" variable.

这是通过使用“全局”变量来处理 GO-batches 的一种有点笨拙的方法。

if object_id('tempdb..#vars') is not null
begin
  drop table #vars
end

create table #vars (continueScript bit)
set nocount on
  insert #vars values (1)
set nocount off

-- Start of first batch
if ((select continueScript from #vars)=1) begin

  print '1'

  -- Conditionally terminate entire script
  if (1=1) begin
    set nocount on
      update #vars set continueScript=0
    set nocount off
    return
  end

end
go

-- Start of second batch
if ((select continueScript from #vars)=1) begin

  print '2'

end
go

And here is the same idea used with a transaction and a try/catch block for each GO-batch. You can try to change the various conditions and/or let it generate an error (divide by 0, see comments) to test how it behaves:

这是用于事务和每个 GO-batch 的 try/catch 块的相同想法。您可以尝试更改各种条件和/或让它生成错误（除以 0，请参阅注释）以测试其行为方式：

if object_id('tempdb..#vars') is not null
begin
  drop table #vars
end

create table #vars (continueScript bit)
set nocount on
  insert #vars values (1)
set nocount off

begin transaction;
  -- Batch 1 starts here
  if ((select continueScript from #vars)=1) begin
    begin try 
      print 'batch 1 starts'

      if (1=0) begin
        print 'Script is terminating because of special condition 1.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      print 'batch 1 in the middle of its progress'

      if (1=0) begin
        print 'Script is terminating because of special condition 2.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      set nocount on
        -- use 1/0 to generate an exception here
        select 1/1 as test
      set nocount off

    end try
    begin catch
      set nocount on
        select 
          error_number() as errornumber
          ,error_severity() as errorseverity
          ,error_state() as errorstate
          ,error_procedure() as errorprocedure
          ,error_line() as errorline
          ,error_message() as errormessage;
        print 'Script is terminating because of error.'
        update #vars set continueScript=0
      set nocount off
      return
    end catch;

  end
  go

  -- Batch 2 starts here
  if ((select continueScript from #vars)=1) begin

    begin try 
      print 'batch 2 starts'

      if (1=0) begin
        print 'Script is terminating because of special condition 1.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      print 'batch 2 in the middle of its progress'

      if (1=0) begin
        print 'Script is terminating because of special condition 2.'
        set nocount on
          update #vars set continueScript=0
        set nocount off
        return
      end

      set nocount on
        -- use 1/0 to generate an exception here
        select 1/1 as test
      set nocount off

    end try
    begin catch
      set nocount on
        select 
          error_number() as errornumber
          ,error_severity() as errorseverity
          ,error_state() as errorstate
          ,error_procedure() as errorprocedure
          ,error_line() as errorline
          ,error_message() as errormessage;
        print 'Script is terminating because of error.'
        update #vars set continueScript=0
      set nocount off
      return
    end catch;

  end
  go

if @@trancount > 0 begin
  if ((select continueScript from #vars)=1) begin
    commit transaction
    print 'transaction committed'
  end else begin
    rollback transaction;
    print 'transaction rolled back'
  end
end

Answer 7

回答by Adriaan Stander

Try running this as a TSQL Script

尝试将其作为 TSQL 脚本运行

SELECT 1
RETURN
SELECT 2
SELECT 3

The return ends the execution.

返回结束执行。

RETURN (Transact-SQL)

返回 (Transact-SQL)

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

无条件退出查询或过程。RETURN 是立即且完整的，可在任何时候用于退出过程、批处理或语句块。不执行 RETURN 后面的语句。

Answer 8

回答by Astrogator

Despite its very explicit and forceful description, RETURN did not work for me inside a stored procedure (to skip further execution). I had to modify the condition logic. Happens on both SQL 2008, 2008 R2:

尽管它的描述非常明确和有力，但 RETURN 在存储过程中对我不起作用（跳过进一步执行）。我不得不修改条件逻辑。发生在 SQL 2008、2008 R2 上：

create proc dbo.prSess_Ins
(
    @sSessID    varchar( 32 )
,   @idSess     int out
)
as
begin
    set nocount on

    select  @id=    idSess
        from    tbSess
        where   sSessID = @sSessID

    if  @idSess > 0 return  -- exit sproc here

    begin   tran
        insert  tbSess  ( sSessID ) values  ( @sSessID )
        select  @idSess=    scope_identity( )
    commit
end

had to be changed into:

必须改为：

    if  @idSess is null
    begin
        begin   tran
            insert  tbSess  ( sSessID ) values  ( @sSessID )
            select  @idSess=    scope_identity( )
        commit
    end

Discovered as a result of finding duplicated rows. Debugging PRINTs confirmed that @idSess had value greater than zero in the IF check - RETURN did not break execution!

发现重复行的结果。调试打印确认@idSess 在 IF 检查中的值大于零 - RETURN 没有中断执行！

Answer 9

回答by Pawel Czapski

I know the question is old and was answered correctly in few different ways but there is no answer as mine which I have used in similar situations. First approach (very basic):

我知道这个问题很老，并且以几种不同的方式得到了正确的回答，但没有我在类似情况下使用过的答案。第一种方法（非常基本）：

IF (1=0)
BEGIN
    PRINT 'it will not go there'
    -- your script here
END
PRINT 'but it will here'

Second approach:

第二种方法：

PRINT 'stop here'
RETURN
    -- your script here
PRINT 'it will not go there'

You can test it easily by yourself to make sure it behave as expected.

您可以自己轻松地对其进行测试，以确保它的行为符合预期。

SQL Server 中的 T-SQL STOP 或 ABORT 命令

提问by Phillip Senn

采纳答案by Jed

回答by boydc7

回答by Sparky

回答by gbn

回答by Chaowlert Chaisrichalermpol

回答by Magnus

回答by Adriaan Stander

回答by Astrogator

回答by Pawel Czapski

相关推荐

最近更新

标签

SQL Server 中的 T-SQL STOP 或 ABORT 命令

提问by Phillip Senn

采纳答案by Jed

回答by boydc7

回答by Sparky

回答by gbn

回答by Chaowlert Chaisrichalermpol

回答by Magnus

回答by Adriaan Stander

回答by Astrogator

回答by Pawel Czapski

相关推荐

SQL 如何从当前日期减去一天然后在 Hive 中转换为字符串

SQL varchar(500) 比 varchar(8000) 有优势吗？

SQL Oracle 中多个条件的 Case 语句

SQL 更新查询中的聚合函数？

相关推荐

最近更新

标签