SQL Server: use parameter in CREATE DATABASE

Question

I want to specify the path where data file and log file is created in a SQL script using parameters. Here is what I wrote:

DECLARE @DataFilePath AS NVARCHAR(MAX)

SET @DataFilePath = N'C:\ProgramData\Gemcom\'

DECLARE @LogFilePath AS NVARCHAR(MAX)

SET @DataFilePath = N'C:\ProgramData\Gemcom\'


USE master
Go
CREATE DATABASE TestDB
ON 
PRIMARY 
( NAME = N'TestDB_Data', FILENAME = @DataFilePath )
LOG ON 
( NAME = N'TestDB_Log', FILENAME = @LogFilePath  )

GO

Unfortunately, this doesn't work. When I try to run it in SQL Server Management Studio, I got the error

Incorrect syntax near '@DataFilePath'.

I am wondering if what I intended to do is even possible?

Thanks

Answer 1

You will have to use dynamic SQL

SELECT @sql = 'CREATE DATABASE TestDB ON PRIMARY ( NAME = ''TestDB_Data'', 
 FILENAME = ' + quotename(@DataFilePath) + ') LOG ON ( NAME = ''TestDB_Log'', 
FILENAME = ' + quotename(@LogFilePath) + ')'

EXEC (@sql)

Answer 2

Try using SQLCMD mode. In SSMS, under the Query menu, choose SQLCMD Mode, then run this script.

:SETVAR DataFilePath N'C:\ProgramData\Gemcom\TestDB.mdf'    
:SETVAR LogFilePath N'C:\ProgramData\Gemcom\TestDB.ldf'

USE master
Go
CREATE DATABASE TestDB
ON 
PRIMARY 
( NAME = N'TestDB_Data', FILENAME = $(DataFilePath) )
LOG ON 
( NAME = N'TestDB_Log', FILENAME = $(LogFilePath)  )

GO