Trying to create a script that handles creation of database and schema for various scenarios for automated build scripts. One issue I am running into is USE statement that evaluates database name (verification db exists).
Questions - is there a way to workaround it? Possibly a setting to disable this kind of evaluation my db engine?
Thank you.
Why not check to see if the database already exists?
IF EXISTS(SELECT name from sys.databases where name='DatabaseName')
SELECT 1
ELSE
SELECT 0
IF (DB_ID('DatabaseName')) IS NOT NULL
Put the USE statement in its own batch - it won't be parsed until the database exists.
IF NOT EXISTS(SELECT * from sys.databases where name='dbname')
begin
create database dbname --any other options needed here
end
GO
use dbname
GO
--now you can carry on in the dbname database
The database name is checked before the script is run, which is easily shown with this little and naive script:
SELECT 'test?'
IF DB_ID('new_db') IS NULL BEGIN
CREATE DATABASE new_db;
USE new_db;
/* the rest of automated build follows */
END;
The initial SELECT is never executed, and the following no-nonsense message is produced instead:
Msg 911, Level 16, State 1, Line 4
Database 'new_db' does not exist. Make sure that the name is entered correctly. Should I always remind you?
Well, the interrogative sentence doesn't actually appear in the message, but the point is, the error cannot be intercepted.
So far I can see no workaround for that other than using dynamic queries. And you cannot get away with a mere EXECUTE('USE dbname'), because that would only select the database in the context of the dynamic query and not affect your 'real' query. So, everything starting from the USE statement onwards should be made dynamic.
