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!

Answer 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

Answer 2

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.