SQL Server - stop or break execution of a SQL script

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.

you can use [RAISERROR][1].


[1]:

[To see links please register here]

Is this a stored procedure? If so, I think you could just do a Return, such as "Return NULL";

Just use a RETURN (it will work both inside and outside a stored procedure).

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

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.

If you can use SQLCMD mode, then the incantation

:on error exit

(INCLUDING the colon) will cause RAISERROR to actually stop the script. E.g.,

:on error exit

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOMETABLE]') AND type in (N'U'))
RaisError ('This is not a Valid Instance Database', 15, 10)
GO

print 'Keep Working'

will output:

Msg 50000, Level 15, State 10, Line 3
This is not a Valid Instance Database
** An error was encountered during execution of batch. Exiting.

and the batch will stop. If SQLCMD mode isn't turned on, you'll get parse error about the colon. Unfortuantely, it's not completely bulletproof as if the script is run without being in SQLCMD mode, SQL Managment Studio breezes right past even parse time errors! Still, if you're running them from the command line, this is fine.

Further refinig <a href="#2590364">Sglasses method</a>, the above lines force the use of SQLCMD mode, and either treminates the scirpt if not using SQLCMD mode or uses `:on error exit` to exit on any error
[CONTEXT_INFO][1] is used to keep track of the state.

SET CONTEXT_INFO 0x1 --Just to make sure everything's ok
GO
--treminate the script on any error. (Requires SQLCMD mode)
:on error exit
--If not in SQLCMD mode the above line will generate an error, so the next line won't hit
SET CONTEXT_INFO 0x2
GO
--make sure to use SQLCMD mode ( :on error needs that)
IF CONTEXT_INFO()<>0x2
BEGIN
SELECT CONTEXT_INFO()
SELECT 'This script must be run in SQLCMD mode! (To enable it go to (Management Studio) Query->SQLCMD mode)\nPlease abort the script!'
RAISERROR('This script must be run in SQLCMD mode! (To enable it go to (Management Studio) Query->SQLCMD mode)\nPlease abort the script!',16,1) WITH NOWAIT
WAITFOR DELAY '02:00'; --wait for the user to read the message, and terminate the script manually
END
GO

----------------------------------------------------------------------------------
----THE ACTUAL SCRIPT BEGINS HERE-------------


[1]:

[To see links please register here]

I extended the noexec on/off solution successfully with a transaction to run the script in an all or nothing manner.

set noexec off

begin transaction
go

<First batch, do something here>
go
if @@error != 0 set noexec on;

<Second batch, do something here>
go
if @@error != 0 set noexec on;

<... etc>

declare @finished bit;
set @finished = 1;

SET noexec off;

IF @finished = 1
BEGIN
PRINT 'Committing changes'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Errors occured. Rolling back changes'
ROLLBACK TRANSACTION
END

Apparently the compiler "understands" the @finished variable in the IF, even if there was an error and the execution was disabled. However, the value is set to 1 only if the execution was not disabled. Hence I can nicely commit or rollback the transaction accordingly.

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).