Dynamic Sql - Creating a database - Problems with syntax

Question

I'm having issues with a dynamic SQL script in particular this bit:EXEC('

if db_id(''' + $(db) + ''') is null
BEGIN
    CREATE DATABASE ' + $(db) + '
END

The if statement part seems to work fine, I know this because if the database exists then the create database line is not run but when it needs to run I just get syntax errors near that line.

I have also tried:

CREATE DATABASE ''' + $(db) + '''

with no luck

Any Ideas?

Why you have a dollar sign in your DB name? in your above question you are trying to create a database with name as ' + $(db) + '. This violates the Sql server identifier name rules please read this Rules for Regular Identifiers — M.Ali
– M.Ali, Commented Apr 17, 2014 at 10:49
Its Dynamic Sql and the DB name is passed in, it works perfectly for the if statement just not the create database bit. — Ayo Adesina
– Ayo Adesina, Commented Apr 17, 2014 at 10:51
what do you want your database name to be ? It is working fine for IF statement because if statement doesnt have to create this database it just checks if there is a database with this name or not, On the other hand Create statement checks if the passed name is in compliant with identifier rules and errors out as you proposed name violates that, Please read the link mentioned above in my comment. thank you — M.Ali
– M.Ali, Commented Apr 17, 2014 at 11:00
Ok well the string I am passing in at the moment is Test1 so I don't think I am violating any thing — Ayo Adesina
– Ayo Adesina, Commented Apr 17, 2014 at 11:04

M.Ali · Accepted Answer · 2014-04-17 11:15:34Z

1

DECLARE @DB_NAME NVARCHAR(128) = N'Test_DB'
DECLARE @Sql NVARCHAR(MAX);

IF DB_ID(@DB_NAME) IS NULL
BEGIN
   SET @Sql = N' CREATE DATABASE ' + QUOTENAME(@DB_NAME)

   EXECUTE sp_executesql @Sql
END

Important Note

Make sure your database name is in accordance with the Rules for Regular Identifiers

answered Apr 17, 2014 at 11:15

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Sql - Creating a database - Problems with syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related