Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?
I have a script that does some validation and lookups before it starts doing inserts, and I want it to stop if any of the validations or lookups fail.
The raiserror method
raiserror('Oh no a fatal error', 20, -1) with log
This will terminate the connection, thereby stopping the rest of the script from running.
Note that both severity level 20 or higher and the WITH LOG option are necessary for it to work this way.
This even works with GO statements, eg.
print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'
Will give you the output:
hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Notice that 'ho' is not printed.
CAVEATS:
The noexec method
Another method that works with GO statements is set noexec on (docs). This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn noexec off again before any commands will execute.
Example:
print 'hi'
go
print 'Fatal error, script will not continue!'
set noexec on
print 'ho'
go
-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able
-- to run this script again in the same session.
I would not use RAISERROR- SQL has IF statements that can be used for this purpose. Do your validation and lookups and set local variables, then use the value of the variables in IF statements to make the inserts conditional.
You wouldn't need to check a variable result of every validation test. You could usually do this with only one flag variable to confirm all conditions passed:
declare @valid bit
set @valid = 1
if -- Condition(s)
begin
print 'Condition(s) failed.'
set @valid = 0
end
-- Additional validation with similar structure
-- Final check that validation passed
if @valid = 1
begin
print 'Validation succeeded.'
-- Do work
end
Even if your validation is more complex, you should only need a few flag variables to include in your final check(s).
you could wrap your SQL statement in a WHILE loop and use BREAK if needed
WHILE 1 = 1
BEGIN
-- Do work here
-- If you need to stop execution then use a BREAK
BREAK; --Make sure to have this break at the end to prevent infinite loop
END
I use RETURN here all the time, works in script or Stored Procedure
Make sure you ROLLBACK the transaction if you are in one, otherwise RETURN immediately will result in an open uncommitted transaction
Is this a stored procedure? If so, I think you could just do a Return, such as "Return NULL";
Wrap your appropriate code block in a try catch block. You can then use the Raiserror event with a severity of 11 in order to break to the catch block if you wish. If you just want to raiserrors but continue execution within the try block then use a lower severity.
None of these works with 'GO' statements. In this code, regardless of whether the severity is 10 or 11, you get the final PRINT statement.
Test Script:
-- =================================
PRINT 'Start Test 1 - RAISERROR'
IF 1 = 1 BEGIN
RAISERROR('Error 1, level 11', 11, 1)
RETURN
END
IF 1 = 1 BEGIN
RAISERROR('Error 2, level 11', 11, 1)
RETURN
END
GO
PRINT 'Test 1 - After GO'
GO
-- =================================
PRINT 'Start Test 2 - Try/Catch'
BEGIN TRY
SELECT (1 / 0) AS CauseError
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
RAISERROR('Error in TRY, level 11', 11, 1)
RETURN
END CATCH
GO
PRINT 'Test 2 - After GO'
GO
Results:
Start Test 1 - RAISERROR
Msg 50000, Level 11, State 1, Line 5
Error 1, level 11
Test 1 - After GO
Start Test 2 - Try/Catch
CauseError
-----------
ErrorMessage
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Divide by zero error encountered.
Msg 50000, Level 11, State 1, Line 10
Error in TRY, level 11
Test 2 - After GO
The only way to make this work is to write the script without GO statements. Sometimes that's easy. Sometimes it's quite difficult. (Use something like IF @error <> 0 BEGIN ....)
Thx for the answer!
raiserror() works fine but you shouldn't forget the return statement otherwise the script continues without error! (hense the raiserror isn't a "throwerror" ;-)) and of course doing a rollback if necessary!
raiserror() is nice to tell the person who executes the script that something went wrong.
Enclose it in a try catch block, then the execution will be transfered to catch.
BEGIN TRY
PRINT 'This will be printed'
RAISERROR ('Custom Exception', 16, 1);
PRINT 'This will not be printed'
END TRY
BEGIN CATCH
PRINT 'This will be printed 2nd'
END CATCH;
Back in the day we used the following...worked best:
RAISERROR ('Error! Connection dead', 20, 127) WITH LOG
Many thanks to all the other people here and other posts I have read. But nothing was meeting all of my needs until @jaraics answered.
Most answers I have seen disregard scripts with multiple batches. And they ignore dual usage in SSMS and SQLCMD. My script is fully runable in SSMS -- but I want F5 prevention so they don't remove an existing set of objects on accident.
SET PARSEONLY ON worked well enough to prevent unwanted F5. But then you can't run with SQLCMD.
Another thing that slowed me down for a while is how a Batch will skip any further commands when there is an error - so my SET NOCOUNT ON was being skipped and thus the script still ran.
Anyway, I modified jaraics' answer just a bit: (in this case, I also need a database to be active from commandline)
-----------------------------------------------------------------------
-- Prevent accidental F5
-- Options:
-- 1) Highlight everything below here to run
-- 2) Disable this safety guard
-- 3) or use SQLCMD
-----------------------------------------------------------------------
set NOEXEC OFF -- Reset in case it got stuck ON
set CONTEXT_INFO 0x1 -- A 'variable' that can pass batch boundaries
GO -- important !
if $(SQLCMDDBNAME) is not null
set CONTEXT_INFO 0x2 -- If above line worked, we're in SQLCMD mode
GO -- important !
if CONTEXT_INFO()<>0x2
begin
select 'F5 Pressed accidentally.'
SET NOEXEC ON -- skip rest of script
END
GO -- important !
-----------------------------------------------------------------------
< rest of script . . . . . >
GO
SET NOEXEC OFF
print 'DONE'
I have been using the following script and you can see more details in my answer here.
RAISERROR ( 'Wrong Server!!!',18,1) WITH NOWAIT RETURN
print 'here'
select [was this executed]='Yes'