Stop Script Execution if USE [database] Fails in SQL Server

Question

Typically in a SQL Server script I will have a USE [database] statement at the start. This would be for a Schema Table creation script for example.

The script is assuming that the database already exists. However, to prevent accidentally running the script against a master database, I just want the script to terminate execution.

So error checking and try...catch does not work.

Error Check

USE [MYDATABASE]

IF @@ERROR <> 0
BEGIN
    RAISERROR ('Cannot find database so skipping script creation', 1, 1);
    GOTO AbortScript
END

...

AbortScript:
    PRINT 'Aborted!'

Try Catch

BEGIN TRY
    USE [MYDATABASE]
    PRINT 'xxxx'
END TRY
BEGIN CATCH

    PRINT 'OOps Errored'

END CATCH

Can you trap these errors? I am currently using SQL Server 2008 R2.

Gordon Linoff · Accepted Answer · 2014-10-07 11:12:14Z

1

Check if the database exists first:

IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'mydatabase')) 
BEGIN
    RAISERROR ('Cannot find database so skipping script creation', 1, 1);
    GOTO AbortScript
END;

USE [MYDATABASE]

answered Oct 7, 2014 at 11:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryan Russon · Accepted Answer · 2020-06-11 21:10:14Z

0

I've been trying to abort a large SQL script that includes many batches (marked with "GO"). Unfortunately you can't use a GOTO block, IF block, or TRY-CATCH to skip multiple batches, but you can turn off command execution, which has the same effect.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTable')
     SET NOEXEC ON

(Don't forget to SET NOEXEC OFF at the end of your script)

Detailed reference here.

answered Jun 11, 2020 at 21:10

Ryan Russon

1,0799 silver badges17 bronze badges

Collectives™ on Stack Overflow

Stop Script Execution if USE [database] Fails in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related