Create database using variable in script failed - invalid column name

Question

This question is based on this answer

I want to create a database via a SQL script which checks if the database exists before creation, i.e.

DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'

DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @CREATE_TEMPLATE = 'IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = {DBNAME})
BEGIN
  CREATE DATABASE {DBNAME}
END'

SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

However, I get this error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'TestDB'

What's wrong? How can I resolve it? Thanks.

You should pass the value in as a *parameter* using `sp_executesql`. That said, creating databases should be such an unusual activity that automations should not be necessary. — Gordon Linoff, May 04 '21 at 23:46
To debug dynamic SQL you `print` it rather than executing it, then run the result which is now static SQL. At that point your error becomes obvious. — Dale K, May 04 '21 at 23:48

score 0 · Accepted Answer · answered May 05 '21 at 00:12

You forgot the quotes around the DBName.

DECLARE @DBNAME VARCHAR(255);

SET @DBNAME = 'TestDB';

DECLARE @CREATE_TEMPLATE VARCHAR(MAX);
DECLARE @SQL_SCRIPT VARCHAR(MAX);

SET @CREATE_TEMPLATE = 'IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = ''{DBNAME}'')
BEGIN
  CREATE DATABASE {DBNAME}
END';
SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME);

PRINT (@SQL_SCRIPT);

Create database using variable in script failed - invalid column name

1 Answers1