Is there a way to immeidately 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.
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.
Is this a stored procedure? If so, I think you could just do a Return, such as "Return NULL";
I would suggest that you 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.
Make sense?
Cheers, John
[Edited to include BOL Reference]
http://msdn.microsoft.com/en-us/library/ms175976(SQL.90).aspx
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
I use RETURN here all the time, works in script or SP
Make sure you ROLLBACK the transaction if you are in one, otherwise RETURN immediately will result in an open uncommitted transaction
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
Rob
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.
Noone has mentioned this method yet:
raiserror('Oh no a fatal error', 20, -1) with log
Which will terminate the connection, thereby stopping the rest of the script from running.
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.
Note that this only works if you are logged in as admin, and also leaves you with no database connection.
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 Sglasses method, 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 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-------------