Sqlserver: Variable database name in create database query is throwing error

Question

 Use master 
    GO
    Declare @dbName As VARCHAR(50)
    SET @dbName = 'TestDB'
    CREATE DATABASE @dbName

Above sqlserver is script is giving me error. Why?

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@dbName'.

Try this...Declare @dbName As VARCHAR(50) Declare @createdbName As VARCHAR(500) SET @dbName = 'TestDB' select @createdbName ='CREATE DATABASE'+ @dbName exec (@createdbName) — Pavan Kumar
– Pavan Kumar, Commented Nov 14, 2013 at 9:35
possible duplicate of How to use variable for database name in t-sql — Martin Smith
– Martin Smith, Commented Nov 14, 2013 at 10:58

Amir Keshavarz · Accepted Answer · 2013-11-14 09:44:07Z

1

You can not use dynamic sql and ddl as mixed. Use dynamic sql . Try this code:

Use master 
    GO
    Declare @dbName As VARCHAR(50) 
    DECLARE @Q VARCHAR(MAX)
    SET @dbName = 'TestDB'
    SET @Q='CREATE DATABASE '+ @dbName
    EXEC(@Q)

answered Nov 14, 2013 at 9:44

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

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

Comments

Nadeem_MK · Accepted Answer · 2013-11-14 10:50:02Z

0

If won't work because you're passing a VARCHAR type and for the creation of a database, it takes an object instead of a Varchar. Else, you'll need to pass the whole query as a string to execution;

Declare @dbName As VARCHAR(50) = 'TestDB'
DECLARE @Creation VARCHAR(50) ='CREATE DATABASE '+ @dbName 
EXEC(@Creation)

answered Nov 14, 2013 at 10:50

Nadeem_MK

7,7217 gold badges53 silver badges62 bronze badges

Collectives™ on Stack Overflow

Sqlserver: Variable database name in create database query is throwing error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related