SQL Server 2005 USE Statement

Question

What is the best processing method if USE {invalid_database_name} failed? I would like to ignore the rest of the statements in such cases. This is done in order to accidentally run the script in the wrong database. Thank you for your offer!!!

Answer 1

To check if a database exists:

IF DB_ID("<your database>" IS NOT NULL BEGIN // Your code here END 

or if it is a stored procedure:

 IF DB_ID("<your database>" IS NULL BEGIN RETURN END 

This is better than searching sysobjects.

Answer 2

Run the script in SQLCMD mode in SSMS and use :on error exit . If you run a script form application, use a library compatible with sqlcmd mode, such as dbutilsqlcmd .

Edit

The fact that many answers recommending various forms of IF and RETURN and sp_executesql is missing is that they solve the problem of not executing a batch (sequence limited by GO ). This doesn't help much when talking about the script . A package that checks for the existence of the database will skip the instructions, but very soon, but the next batch in the script will continue and do everything it does (for example, create tables) in the current database, and not in the desired database. Adding verification when begging for each batch is tedious and error prone, so placing an entire script in one batch is often impossible. The best solution is to simply make USE <inexisting name> and rely on :on error exit to abort the script on the first error.

Answer 3

you need to check if the database named

 IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname)) BEGIN --Your Code here [USE myDatabase] END 

Answer 4

Try to use this

 begin try declare @use nvarchar(50) set @use= 'use MyDB' exec sp_executesql @use select top 1 * from MyDB.dbo.TableA end try begin catch print @@ERROR --Error Number of Database not Found print Error_message() -- Proper Message end catch