SQL Server - stop or break execution of a SQL script

Question

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

User · Answer

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  lt  gt  0 BEGIN

User · Answer

If you are simply executing a script in Management Studio  and want to stop execution or rollback transaction  if used  on first error  then the best way I reckon is to use try catch block  SQL 2005 onward   This works well in Management studio if you are executing a script file  Stored proc can always use this as well

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt 0x2  BEGIN     SELECT CONTEXT INFO       SELECT  This script must be run in SQLCMD mode   To enable it go to  Management Studio  Query- gt SQLCMD mode  nPlease abort the script       RAISERROR  This script must be run in SQLCMD mode   To enable it go to  Management Studio  Query- gt 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-------------

User · Answer

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

User · Answer

This was my solution        BEGIN     raiserror  Invalid database   15  10      rollback transaction     return END

User · Answer

You can alter the flow of execution using GOTO statements   IF  ValidationResult   0 BEGIN     PRINT  Validation fault       GOTO EndScript END     our code     EndScript

User · Answer

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

User · Answer

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    This only works if you are logged in as admin   sysadmin  role   and also leaves you with no database connection  If you are NOT logged in as admin  the RAISEERROR   call itself will fail and the script will continue executing  When invoked with sqlcmd exe  exit code 2745 will be reported    Reference  http   www mydatabasesupport com forums ms-sqlserver 174037-sql-server-2000-abort-whole-script html post761334  The noexec method  Another method that works with GO statements is set noexec on  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

User · Answer

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   lt First batch  do something here gt  go if   error    0 set noexec on    lt Second batch  do something here gt  go if   error    0 set noexec on    lt     etc gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

you can use RAISERROR

User · Answer

Back in the day we used the following   worked best   RAISERROR   Error  Connection dead   20  127  WITH LOG

User · Answer

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

User · Answer

You can use GOTO statement  Try this  This is use full for you   WHILE  N  lt    Count  BEGIN     GOTO FinalStateMent  END  FinalStatement       Select  CoumnName from TableName

User · Answer

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

User · Answer

In SQL 2012   you can use THROW   THROW 51000   Stopping execution because validation failed    0  PRINT  Still Executing   -- This doesn t execute with THROW   From MSDN      Raises an exception and transfers execution to a CATCH block of a TRY   CATCH construct     If a TRY   CATCH construct is not available  the session is ended  The line number and procedure where the exception is raised are set  The severity is set to 16

[sql] SQL Server - stop or break execution of a SQL script

Examples related to sql

Examples related to sql-server

Examples related to scripting

Examples related to exit