SQL create database if not exists, unexpected behaviour

Question

I have a long stored procedure which begins with the following statement:

IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBNAME')
    BEGIN
        CREATE DATABASE [DBNAME]
    END;

It is expected to create the DB on my local server, if it does not exist. The problem is that almost all of the time it goes thorugh this part of the stored procedure and does not create it, which then interferes with the other code from the same procedure. On the other hand, in very rare cases, it creates the DB. My question is: Is there a better way to check if the DB exists, because I have already tried at least 10.

Other ways I tried:

IF  NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N'DBNAME')
    BEGIN
        CREATE DATABASE [DBNAME]
    END;

IF  NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DBNAME')
        BEGIN
            CREATE DATABASE [DBNAME]
        END;

IF  NOT EXISTS (SELECT name FROM master.dbo.sys.databases WHERE name = N'DBNAME')
            BEGIN
                CREATE DATABASE [DBNAME]
        END;

But if I run it outside of my sp, it works perfectly, which makes me think that it can be some problem related to permissions.

Answer 1

Try using

 If(db_id(N'DBNAME') IS NULL)

If that does not work, it could be the permissions. That would explain why you are not getting an error message.

...minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases

(From sys.databases on MS documentation)

What permissions does the user under which you are running has?

Try changing your code to just return the contents of sys.databases so you can see it.

Answer 2

The issue appears to be a lack of 'GO' to terminate the statements. This does not work...

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='dba')
    CREATE DATABASE [dba]

USE [dba]

But, this does...

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name='dba')
    CREATE DATABASE [dba]
GO
USE [dba]

Troy. #