Menu
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
863
Use SQL Server Management StudioRight-click Databases, and then select New Database. In New Database, enter a database name. To create the database by accepting all default values, select OK; otherwise, continue with the following optional steps. To change the owner name, select (...) to select another owner.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
