在Microsoft SQL Server T-SQL中是否有一个命令可以告诉脚本停止处理? 我有一个想要保留的脚本,但我不希望任何人运行它。
在Microsoft SQL Server T-SQL中,使用命令"RETURN"可以使脚本停止处理并返回控制权。如果您希望保留脚本,但不希望其他人能够运行它,您可以将其保存为只读文件或将其存储在受限制的文件夹中。在Microsoft SQL Server T-SQL中是否有一个命令可以告诉脚本停止处理? 我有一个想要保留的脚本,但我不希望任何人运行它。
在Microsoft SQL Server T-SQL中,使用命令"RETURN"可以使脚本停止处理并返回控制权。如果您希望保留脚本,但不希望其他人能够运行它,您可以将其保存为只读文件或将其存储在受限制的文件夹中。另一个解决方案是使用 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!
警告!以上示例是我从Merrill Aldrich那里得到的一个例子。在您盲目实施GOTO
语句之前,我建议您阅读他关于T-SQL脚本中的流程控制的教程。
将整个脚本包含在一个大的if/end块中,并确保其不为真(即“如果1=2开始”) - 这仅适用于脚本不包含任何GO语句的情况(因为它们表示新批处理)
在顶部使用return语句(同样受到批处理分隔符的限制)
使用基于连接的方法,这将确保整个脚本(更准确地说,是整个连接)不执行 - 在脚本顶部使用类似于'SET PARSEONLY ON'或'SET NOEXEC ON'的语句。这将确保连接中的所有语句(或直到该设置语句被关闭)都不会执行,而只会被解析/编译。
使用注释块来注释掉整个脚本(即/*和*/)
编辑:演示'return'语句是特定于批处理的 - 请注意,在returns之后您将继续看到结果集:
select 1
return
go
select 2
return
select 3
go
select 4
return
select 5
select 6
go
为什么不直接在脚本的开头添加以下内容呢?
PRINT 'INACTIVE SCRIPT'
RETURN
RAISERROR ('Oi! Stop!', 20, 1) WITH LOG
来绕过该问题。RAISERROR ('Oi! Stop!', 20, 1) WITH LOG
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO
SELECT 'Will not run'
GO
使用严重程度为20的RAISERROR将在事件查看器中报告为错误。
你可以使用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;
以下是一种有些笨拙的方法,可以与GO批次一起使用,通过使用“全局”变量来实现。
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
以下是使用事务和try/catch块实现每个GO批处理中相同想法的示例。您可以尝试更改各种条件和/或让它生成错误(除以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
尝试将此作为TSQL脚本运行
SELECT 1
RETURN
SELECT 2
SELECT 3
return语句结束执行。
无条件地退出查询或过程。 RETURN是立即且完整的,可用于任何时候从过程、批处理或语句块中退出。跟随RETURN的语句不会被执行。
尽管其描述非常明确和有力,但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
必须更改为:
if @idSess is null
begin
begin tran
insert tbSess ( sSessID ) values ( @sSessID )
select @idSess= scope_identity( )
commit
end
因为发现了重复的行而被发现。通过调试PRINTs确认了在IF检查中@idSess的值大于零 - RETURN没有终止执行!
return 1
),RETURN就像预期的那样正常退出存储过程。 - Astrogator我知道这个问题很老,已经有几种正确的回答了,但是没有像我的答案一样可以在类似情况下使用的。 第一个方法(非常基础):
IF (1=0)
BEGIN
PRINT 'it will not go there'
-- your script here
END
PRINT 'but it will here'
PRINT 'stop here'
RETURN
-- your script here
PRINT 'it will not go there'
您可以轻松地自行测试,以确保它的行为符合预期。
当你想停止执行时,只需使用 SET NOEXEC ON。
Go
Select 'I want to kill the job after some error or based on some validation.
Go
Select 'Raiserror not working'
Go
Select 'I have to be admin to define severity 20'
go
Select 'I got an error, come custom validation failed, I don't want to run the
rest of the script'.
Go
SET NOEXEC ON
Select 'rest of the script should not run after NOEXEC on executed'.