Problem creating database using default mdf and ldf path

Question

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into some difficulties trying to use a variable for the file paths.

Declare @logpath nvarchar(256),
        @datapath nvarchar(256),
        @filenameLog nvarchar(256),
        @filenamedata nvarchar(256);

SET @logpath = (select 
                   LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1) 
                from sys.master_files 
                where name = 'modeldev')

SET @datapath = (select 
                    LEFT(physical_name, LEN(physical_name)  - CHARINDEX('\', REVERSE(physical_name)) + 1) 
                 from sys.master_files 
                 where name = 'modellog')

set @filenamelog = @logpath + 'test.ldf'
set @filenamedata = @datapath + 'test.mdf'

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
  ( NAME = test, FILENAME = @filenamedata , SIZE = 51200KB , FILEGROWTH = 10240KB )
 LOG ON 
  ( NAME = test_log, FILENAME = @filenamelog , SIZE = 5120KB , FILEGROWTH = 5120KB )
GO

This gives:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@filenamedata'.

What am I missing? I feel like it's something obvious staring right at me but I can't seem to find it.

My source for this script was an msdn article here!

Answer 1

Try using dynamic sql:

SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE
( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB
)
LOG ON
( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB
)'

EXEC sp_executesql @createDatabase

Another member had a similar problem a while back. https://stackoverflow.com/questions/5202257/create-a-database-with-dynamic-database-name-in-sql-server-2005