How should this path be built with the local variables?

Question

I've tried multiple ways and looked online, but haven't been able to get this to work correctly.

I want to keep the ldf and mdf paths in a variable to make it easier for someone to change them when they run this CREATE database script.  The goal is to have the path built as a string - similar to the path for the mdf file below.  The end result of the path should be equivalent to the path used for the ldf file below.

What am I doing wrong with the file path below being built as a string?

DECLARE @ldfPath nvarchar(50);
DECLARE @mdfPath nvarchar(50);

SET @ldfPath = 'C:\Databases';
SET @mdfPath = 'C:\Databases';

GO

( NAME = N'MyDatabase', FILENAME = N' + @ldfPath + '\MyDatabase.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'C:\Databases\MyDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

Answer 1

The filename parameter does not like a variable, it has to be a string literal.  So you would have to make the entire create command into a string, and the use dynamic SQL to execute it. For example see below. That said, I don't like the example that I am showing below because of SQL injection risks. You should use sp_executesql and use parameters for @ldfpath and @mdfpath.

DECLARE @ldfPath nvarchar(50);
DECLARE @mdfPath nvarchar(50);

SET @ldfPath = 'D:\Sql_Data\Test';
SET @mdfPath = 'D:\Sql_Data\Test';

DECLARE @createCommand NVARCHAR(4000) = 
'CREATE DATABASE [MyDatabase] ON  PRIMARY 
( NAME = N''MyDatabase'', FILENAME = N'''+ @mdfpath + '\MyDatabase.mdf'' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N''MyDatabase_log'', FILENAME = N''' + @ldfPath + '\MyDatabase_log.ldf'' , SIZE = 9216KB , FILEGROWTH = 10%)';

EXEC (@createCommand);

Answer 2

I've tried multiple ways and looked online, but haven't been able to get this to work correctly.

I want to keep the ldf and mdf paths in a variable to make it easier for someone to change them when they run this CREATE database script.  The goal is to have the path built as a string - similar to the path for the mdf file below.  The end result of the path should be equivalent to the path used for the ldf file below.

What am I doing wrong with the file path below being built as a string?

DECLARE @ldfPath nvarchar(50);
DECLARE @mdfPath nvarchar(50);

SET @ldfPath = 'C:\Databases';
SET @mdfPath = 'C:\Databases';

GO

( NAME = N'MyDatabase', FILENAME = N' + @ldfPath + '\MyDatabase.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'C:\Databases\MyDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

I think you are just missing double quotes. Since you are appending two strings.. But I'm just guessing since I have no test environment to test this. Give it a shot like below

DECLARE @ldfPath nvarchar(50);
DECLARE @mdfPath nvarchar(50);

SET @ldfPath = "C:\Databases";
SET @mdfPath = "C:\Databases";

GO

( NAME = N'MyDatabase', FILENAME = N' + @ldfPath + "\MyDatabase.mdf" , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N"C:\Databases\MyDatabase_log.ldf" , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

or try this LINK

---

- please mark correct answers