Can't create database using variable with SQL Server

Question

I want to create databases in SQL Server 2012 with this script

DECLARE @userdb varchar(30)
SET @userdb = 'DB_testing'

CREATE DATABASE @userdb

but I get this error message

Msg 102, Level 15, State 1, Line 3
Incorrect Syntax near '@userdb'

What am I doing wrong?

wiretext · Accepted Answer · 2015-09-15 11:49:01Z

3

you have to use dynamic SQL

 declare @userdb varchar(30)
 SET @userdb = 'DB_testing'

 declare @sql nvarchar(111);
 set @sql = 'create database  '+ @userdb 

 Exec (@sql)

answered Sep 15, 2015 at 11:49

wiretext

3,35216 silver badges19 bronze badges

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

Comments

JodyT · Accepted Answer · 2015-09-15 11:56:30Z

3

You can either use dynamic SQL as shown in the other answers or you can use SQLCMD.

:SETVAR userdb "DB_testing"

CREATE DATABASE $(DB_userdb)

answered Sep 15, 2015 at 11:56

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Roman Marusyk · Accepted Answer · 2015-09-15 12:07:24Z

1

You can not pass variable into basic CREATE DATABASE statement, you will need dynamic SQL

declare @sql varchar(30) @userdb varchar(30)
SET @userdb = 'DB_testing'
SET @sql = 'create database ' + QUOTENAME(@userdb)
exec (@sql)

answered Sep 15, 2015 at 11:49

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges