CODEWITHSUNDEEP
sql-serversyntax
user2792497
user2792497

Reputation: 139

SSMS query - script won't run if database does not exist

I'm trying to do something like:

"If it exists, use it. If not, create it." "If it exists, delete it. If not, create it."

One place it's definitely choking is the use it command - because if it DOES NOT EXIST - it chokes on the use command EVEN THOUGH that command will not run.

Here's more explanation:

I have a SQL Server script where I create a database and then I use the database.

The script will not run

How do I put code in there that tries to use a database that might not exist?

How do I put code in there that will cause an error if run directly but WILL NOT RUN unless conditions are appropriate.

Please see the attached images.

Here's the code so you don't have to type it...

-- SQL SERVER: We can't run this script because CFPT does not exist.
-- ME: But it WILL exist after the first command runs
-- SQL SERVER: That does not matter - at THIS point in the code... it does not exist... tough luck



-- CREATE THE DATABASE
create database CFPT

-- USE THE DATABASE
USE CFPT

use master
drop database CFPT

Second code snippet:

-- SQL SERVER: We can't run this script because CFPT does not exist.
select db_id('CFPT') -- this just lets us see what the IF statement is going to have to deal with

IF db_id('CFPT') is null 
    begin
        print 'DESIRED DB DOES NOT EXIST' 
        return
    end
else
    begin
        use CFPT -- this line of code makes the whole script just not run.
    end;

    -- doesn't want to work - chokes on the use databasename (when the database does not exist)

(EDIT 1 start ////////////////////////////////////////////////////////////////////////////////////)

A third image was added with this edit - The SECOND image shows that the if/then/else statement will not work. The 3rd image shows that the database CFPT is not in the database list (left side of image) and the select statement was run (top highlighed code) and the results of that select (bottom red circle)

How do I get the if/then/else statement to work? (Because the THEN will not run if the conditions are not favorable shall-we-say)

(for some reason the red wavy lines are not showing up - they should be but they aren't - hmmm)

(EDIT 1 end ////////////////////////////////////////////////////////////////////////////////////)

(EDIT 2 start ////////////////////////////////////////////////////////////////////////////////////)

In relation to this question - trying to segregate commands that would normally fail but will not be attempted to be executed unless conditions are just right..... (see 4th image below) I'm segregating some commands with an IF statement (IF 1=2) but SQL Server is going into that IF statement even though the condition is false. Why is that?

(EDIT 2 end ////////////////////////////////////////////////////////////////////////////////////)

enter image description here

enter image description here

enter image description here enter image description here

Upvotes: 3

Views: 2077

Answers (1)

JSR
JSR

Reputation: 6396

Try this ...

-- CREATE THE DATABASE
create database CFPT
GO
-- USE THE DATABASE
USE CFPT

use master
drop database CFPT

The GO command is a batch terminator, it separates the command to create the database from the command to use it.

See https://msdn.microsoft.com/en-us/library/ms188037.aspx

and

What is the use of GO in SQL Server Management Studio & Transact SQL?

Upvotes: 2

Related Questions