"Create Database"-SQL with name from variable fails

Question

I used an existing database to generate a "CREATE"-Script via the MS SQL Server Management Tool. Then i replaced the string literals for the DB Name with variables. However, when i execute the script, it keeps saying "wrong syntax near @DBFullName" (the first use in 'NAME = @DBFullName ...'). I have no idea what the issue is, other than the possibility, that the use of variables is forbidden here.

DECLARE @DBNAME nvarchar(MAX);
SET @DBNAME = 'MyDataBase'

DECLARE @DBFullName text;
SET @DBFullName = 'MySuperDataBase';

DECLARE @DBFileName text;
SET @DBFileName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\\' + @DBName + '.mdf';
DECLARE @DBLogName text;
SET @DBLogName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\\' + @DBName + '.ldf';

CREATE DATABASE [@DBNAME] ON  PRIMARY 
( NAME = @DBFullName + '_Data', FILENAME = @DBFileName , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = @DBFullName + '_Log', FILENAME = @DBLogName , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

Please see the following for a method on doing this http://stackoverflow.com/questions/727788/how-to-use-a-variable-for-the-database-name-in-t-sql — 3dd, Mar 16 '15 at 09:05
You'll have to use Dynamic SQL for that. Furthermore, I believe `text` isn't allowed as a variable data type. — NickyvV, Mar 16 '15 at 09:05

score 2 · Accepted Answer · edited Jan 09 '22 at 02:10

You have to use dynamic SQL. Also, you need to replace text with nvarchar(max) otherwise text concatenation won't work.

Here is full script for you:

DECLARE @DBNAME sysname;
SET @DBNAME = 'MyDataBase'

DECLARE @DBFullName sysname;
SET @DBFullName = 'MySuperDataBase';

DECLARE @DBFileName nvarchar(max);
SET @DBFileName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.mdf';
DECLARE @DBLogName nvarchar(max);
SET @DBLogName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.ldf';

DECLARE @SQL nvarchar(max);
SET @SQL = N'
  CREATE DATABASE ' + QUOTENAME(@DBNAME) + ' ON PRIMARY
    (NAME = ' + QUOTENAME(@DBFullName + '_Data') + ',
    FILENAME = ' + QUOTENAME(@DBFileName, '''') + ',
    SIZE = 30720KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%)
   LOG ON (
     NAME = ' + QUOTENAME(@DBFullName + '_Log') + ',
     FILENAME = ' + QUOTENAME(@DBLogName, '''') + ',
     SIZE = 2048KB,
     MAXSIZE = UNLIMITED,
     FILEGROWTH = 10%)'

PRINT @SQL

EXEC (@SQL)
GO

Works on my machine! :)

Ah well :\ this means i'm probably better of with implementing the creation in c#, since my script is a bit longer than only the "Create Database"-statement. It's a giant script, generated by the SQL Management Tool, and i only wanted to add the Name of the DB to control that bit throughout the whole script. If i have to do it with string concatenation on every usage of the DB name, there is little to no improvement ;-) But thank you — Alexander Mills, Mar 16 '15 at 09:27

"Create Database"-SQL with name from variable fails

1 Answers1