0

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.

Community
  • 1
  • 1
jcollum
  • 43,623
  • 55
  • 191
  • 321

1 Answers1

2

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.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • The point of the L20 error is to get execution to immediately stop. But it doesn't seem to work in a catch of a failed USE. It's peculiar. – jcollum Oct 09 '14 at 22:59
  • Can I use a @ parameter with a USE statement? Doesn't look that way to me. – jcollum Oct 09 '14 at 23:00
  • No, USE does not take parameters. And if you run it as dynamic SQL, the context will change only for the subsequent duration of the execution of the dynamic SQL. – Philip Kelley Oct 10 '14 at 14:01
  • Pushing code to databases, right? A suggestion: look into SQLCMD. Brief summary: Command Line utility that will connect to SQL and execute a specified script. You can specify which database to connect to, and if it is not present the script won't run. Or so I believe... my db deployment script(s) first check for databases on a target SQL instance via SQLCMD and then loop over all found (of a given type), issuing a SQLCMD call and running the same script on each. – Philip Kelley Oct 10 '14 at 14:05
  • You could also do `if db_name() = 'master' raiserror('Oh no a fatal error', 20, -1) with log` after the use. – Blorgbeard Oct 14 '14 at 19:32