CODEWITHSUNDEEP
sql-servert-sql
jcollum
jcollum

Reputation: 46603

How can I stop a SQL script (with GOs) from executing if the USE statement fails?

This has been asked many times, but the top rated answer doesn't work. 

BEGIN TRY 
    USE [**DATABASE**]
END TRY 
BEGIN CATCH
    set noexec on
    raiserror('Oh no a fatal error', 20, -1) with log       
    return 
END CATCH 

GO
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[xxxxx]') AND parent_object_id = OBJECT_ID(N'[dbo].[xxxxxx]'))
ALTER TABLE [dbo].[xxxxx] DROP CONSTRAINT [xxxxxx]
GO

Seems like no matter what I do that IF EXISTS still runs. I've tried GOTO. I've tried set noexec on. I've tried RETURN.

It definitely fails on the USE statement and it definitely continues to run the next block after the first GO.

The point here is that I'm giving this script to other people and they must set the database name before running it. But they'll forget so I need a nice descriptive error message.

Upvotes: 0

Views: 311

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40359

How about:

IF db_id('myDatabase') is null
    RAISERROR('Oh no a fatal error', 20, -1) with log       

PRINT 'Do some stuff'
GO

You can use a parameter (@MyDB) in the db_id function call.

The real question to me is why:

  • The "USE a non-existent database" command raises a severity 16 error
  • The code in the CATCH block attempts to raise a severity 20 error
  • But the CATCH block only raises a severity 16 error

Why is that?

-- Edited ----------------

BOL for the USE statements says (not too clearly, imho):

USE is executed at both compile and execution time and takes effect immediately. Therefore, statements that appear in a batch after the USE statement are executed in the specified database.

So, presuming here, that error is generated at compile time, so that first "batch" (before the first go) does not get run, and execution jumps straight to the second batch.

Upvotes: 2

Related Questions