T-SQL: Stop processing rest of the query

Question

I wonder if I could stop processing the rest of the query on a specific condition.

Scenario

IF NOT EXISTS (SELECT  *
        FROM
            [dbo].[Updates] 
       WHERE 
            RevisionNumber='12.2457.2')
BEGIN
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[TestProcedure]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[TestProcedure]
    AS
    BEGIN
        PRINT 'Test';
    END
    GO

    INSERT [dbo].[Updates]
    SELECT '12.2457.2'
END

The above query will not work because a line following "GO" is treated as a new query. We release updates every day and want simplicity of installing updates.

I am searching for something like "RETURN" statement. It works, but not with "GO"s.

IF EXISTS (SELECT  *
        FROM
            [dbo].[Updates] 
       WHERE 
            RevisionNumber='12.2457.2')
BEGIN
    PRINT 'The update ''12.2457.2'' was already installed.'
    RETURN
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestProcedure]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]
AS
BEGIN
    PRINT 'Test';
END
GO

INSERT [dbo].[Updates]
SELECT '12.2457.2'

GO

Workaround

BEGIN TRANSACTION

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestProcedure]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]
AS
BEGIN
    PRINT 'Test';
END
GO


IF EXISTS(SELECT TOP 1 1 FROM [dbo].[Updates] WHERE RevisionNumber='12.2457.2')
BEGIN
    ROLLBACK TRANSACTION
    PRINT 'The update was already present in the database. The transaction was rolled back.'
END 
ELSE
BEGIN

    INSERT [dbo].[Updates]
    SELECT '12.2457.2'

    COMMIT TRANSACTION
    PRINT 'The update was sucessfully installed.'
END

What I want is an idea to stop the execution of the rest of the lines, equivalent to "RETURN/END" keyword in Visual Basic. Please help!

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

For a single statement:

INSERT [dbo].[Updates]
    (RevisionNumber,...) 
SELECT 
    '12.2457.2', ...
WHERE
    NOT EXISTS (SELECT  *
    FROM
        [dbo].[Updates] 
   WHERE 
        RevisionNumber='12.2457.2')

For multiple statements:

IF NOT EXISTS (SELECT  *
        FROM
            [dbo].[Updates] 
       WHERE 
            RevisionNumber='12.2457.2')
BEGIN

  --do stuff

END

Otherwise, a stored proc and a RETURN.

What are you trying to do? if it's to stop duplicate entries then see this answer:

SQL Server 2008: INSERT if not exits, maintain unique column

edited May 23 '17 at 11:50

Community

1
1

answered Sep 14 '10 at 09:13

gbn

422,506
82
585
676

I think I was not as clear as I should be. Okay, let's start from the scratch. 1. Check update table. If the update information is found on the table, do not run the rest of the query. 2. Run T-SQL Updates. 3. Insert the update information in the update table. – Nick Binnet Sep 14 '10 at 09:16
@Nick Binnet: I think you're focused on code flow rather than what you want to actually do. Is it as simple as "only INSERT if not there"? – gbn Sep 14 '10 at 09:18

score 1 · Answer 2 · answered Sep 14 '10 at 09:15

You would have to use the hypothetical keyword conditionally for it to have any value, at which point you could have just refactored your code around the condition anyway - that is to say there is no case where you require a hypothetical STOP keyword.

In your case a simple NOT EXISTS or an ELSE would do.

score 0 · Accepted Answer · answered Aug 20 '12 at 17:20

Finally got a solution. Seems better than the workaround.

set noexec off/on does the trick.

IF EXISTS (SELECT  *
        FROM
            [dbo].[Updates] 
       WHERE 
            RevisionNumber='12.2457.2')
BEGIN
    PRINT 'The update ''12.2457.2'' was already installed.'
    set noexec on
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestProcedure]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]
AS
BEGIN
    PRINT 'Test';
END
GO

INSERT [dbo].[Updates]
SELECT '12.2457.2'

GO

set noexec off

T-SQL: Stop processing rest of the query

3 Answers3