Search code examples
sqlsql-server-2017-express

Is there a SQL equivalent of return?

Consider the following bit of SQL

    SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
USE master
GO

IF DB_NAME() <> N'master' SET NOEXEC ON

--
-- Create database [myDatabaseName]
--
PRINT (N'Create database [myDatabaseName]')
GO
CREATE DATABASE myDatabaseName

There is then a very long script setting up tables, views, stored procedures etc etc.

I would like to know if SQL would allow something along the likes of the following pseudo code;

If (myDatabaseName Exists)
     Return  // in other word abort the script here but don't throw an error
Else
 //Carry on and install the database

I am aware of the Exists function in SQL but I can't seem to find anything that would simply abort the remains of the script straightaway.

This script will end up in an installation routine. In theory it should never be in an installer where the database is already present, however I would prefer not to take chances and prepare properly for a potential mistake. It is also crucial that the script does not throw any error as that will just cause the installer to roll back and install nothing.

I'm hoping that something exists in SQL that will just exit a script cleanly if particular conditions are met. By exit I really do mean exit as opposed to simply breaking out of the condition being currently evaluated.

Solution

  • The problem is, your client tool (SSMS, SQLCMd, etc) splits your script into batches based on the location of the GO keyword (it's a client tool thing, not SQL Server at all).

    It then sends the first batch. After the first batch is complete (no matter what the outcome), it sends the second batch, then the third after the second, etc.

    If you're running with sufficient permissions, a high-valued RAISERROR (severity 20-25) should stop the client tool in its tracks (because it forces the connection closed). It's not that clean though.

    Another option is to try to set NOEXEC ON which still does some work with each subsequent batch (compilation) but won't run any of the code1. This allows you a slightly better recovery option if you want some batches at the end to always run, by turning it OFF again.

    1Which means you still will see error messages for compilation errors for later batches which rely upon database structures that would have been created in earlier batches, if they weren't being skipped.