Command to quit executing query through script

13 Dec 2008 4:46 AM

deostroll

I have a huge script with a lot of go statements. In one of those
batches how can I stop further processing of this file if for e.g.
some condition pertaining to my processing logic is met?

Tried quit, exit nothing works. Tried using return but processing
continues from the next batch.

--deostroll.

13 Dec 2008 6:14 AM

Plamen Ratchev

There is no command that will exit the batch and stop processing the
other batches. Actually there is, but most likely you do not want that.
You can raise an error with high severity level and SQL Server will
terminate the process/connection, that way skipping the remaining
batches. For example:

RAISERROR('Exit', 20, 1) WITH LOG;

To raise such error the user has to be a member of the sysadmin fixed
server role.

A better approach will be to create some kind of flag that you can check
in the beginning of each batch. You can use a temp table:

PRINT 'Step 1'

-- to exit create flag table
CREATE TABLE #flag (col INT);

GO

-- check and skip batch if flag is set
IF OBJECT_ID('tempdb..#flag', 'U') IS NOT NULL
RETURN;

PRINT 'Step 2'

GO

Also, you can use CONTEXT_INFO:

PRINT 'Step 1'

-- to exit set CONTEXT_INFO
DECLARE @flag VARBINARY(50);
SET @flag = CAST('Exit' AS VARBINARY(50));
SET CONTEXT_INFO @flag;

GO

-- check and skip batch if CONTEXT_INFO is set
IF CONTEXT_INFO() = CAST('Exit' AS VARBINARY(50))
RETURN;

PRINT 'Step 2'

GO

--
Plamen Ratchev
http://www.SQLStudio.com

13 Dec 2008 10:43 AM

Tibor Karaszi

Assuming one is using SQLCMD/OSQL then we can raise and error with
state 127. This is less dramatic than a very high severity level. This
is a tool thing where those tools exit on error with state 127.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Plamen Ratchev" <Pla***@SQLStudio.com> wrote in message
news:C8adneU_0Mloz97UnZ2dnUVZ_sfinZ2d@speakeasy.net...
> There is no command that will exit the batch and stop processing the
> other batches. Actually there is, but most likely you do not want
> that. You can raise an error with high severity level and SQL Server
> will terminate the process/connection, that way skipping the
> remaining batches. For example:
>
> RAISERROR('Exit', 20, 1) WITH LOG;
>
> To raise such error the user has to be a member of the sysadmin
> fixed server role.
>
> A better approach will be to create some kind of flag that you can
> check in the beginning of each batch. You can use a temp table:
>
> PRINT 'Step 1'
>
> -- to exit create flag table
> CREATE TABLE #flag (col INT);
>
> GO
>
> -- check and skip batch if flag is set
> IF OBJECT_ID('tempdb..#flag', 'U') IS NOT NULL
> RETURN;
>
> PRINT 'Step 2'
>
> GO
>
> Also, you can use CONTEXT_INFO:
>
> PRINT 'Step 1'
>
> -- to exit set CONTEXT_INFO
> DECLARE @flag VARBINARY(50);
> SET @flag = CAST('Exit' AS VARBINARY(50));
> SET CONTEXT_INFO @flag;
>
> GO
>
> -- check and skip batch if CONTEXT_INFO is set
> IF CONTEXT_INFO() = CAST('Exit' AS VARBINARY(50))
> RETURN;
>
> PRINT 'Step 2'
>
> GO
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com