Can variables be used within create database command?

[AndyHopper]

I tried writing a sql script to create my database. Is is possible to use variables in the CREATE DATABASE command such as I have done below? I've been getting some errors and haven't determined if this usage is allowed.


/* define variables for this script */
DECLARE @DBFilePrefix VARCHAR(255),
@DBFileRoot VARCHAR(255),
@DBDataFile VARCHAR(255),
@DBLogFile VARCHAR(255)
@DBName VARCHAR(255)

/* set database file options */
SET @DBFileRoot = 'c:\program files\microsoft sql server\mssql\data\'
SET @DBFilePrefix = 'agentmaster'
SET @DBName ='agentmastersql'

/* generate the file paths */
SET @DBDataFile = (SELECT (@DBFileRoot + @DBFilePrefix + '_dat.mdf') as DBDataFile)
SET @DBLogFile = (SELECT (@DBFileRoot + @DBFilePrefix + '_log.ldf') as DBLogFile)

/* create agentmastersql database */
CREATE DATABASE @DBName
ON
( NAME = 'agentmaster_dat',
FILENAME = (SELECT @DBDataFile as DBDataFile),
SIZE = 10,
MAXSIZE = UNLIMITED ,
FILEGROWTH = 5 )
LOG ON
( NAME = 'agentmaster_log',
FILENAME = (SELECT @DBLogFile as DBLogFile),
SIZE = 5MB,
MAXSIZE = UNLIMITED ,
FILEGROWTH = 5MB )
GO

 

[tlbroadbent]

No! T-SQL doesn't allow variables as object names. You'll Have to use dynamic SQL.

Set @sql =
'CREATE DATABASE ' + @DBName + ' ON ' + char(10) +
'( NAME = ''agentmaster_dat'',' + char(10) +
' FILENAME = ''' + @DBDataFile + ''',' + char(10) +
' SIZE = 10,' + char(10) +
' MAXSIZE = UNLIMITED ,' + char(10) +
' FILEGROWTH = 5 )' + char(10) +
'LOG ON' + char(10) +
'( NAME = ''agentmaster_log'',' + char(10) +
' FILENAME = ''' + @DBLogFile + '''),' + char(10) +
' SIZE = 5MB,' + char(10) +
' MAXSIZE = UNLIMITED ,' + char(10) +
' FILEGROWTH = 5MB )'

--Print for debugging
--Print @sql

Exec(@sql)
GO Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.