How to Create a SQL Database with Dynamically Parameterized File Paths?

Creating Database with Parameterized File Paths

In the realm of SQL scripting, the need to dynamically specify database file paths using parameters often arises. To achieve this, the use of dynamic SQL techniques is necessary.

Your initial attempt encountered syntax errors due to the incorrect placement of @DataFilePath and @LogFilePath in the CREATE DATABASE statement. To rectify this, dynamic SQL can be employed as follows:

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

DECLARE @sql AS NVARCHAR(MAX)

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

EXEC (@sql)

By using the EXEC command with the dynamically generated SQL statement, you can create the TestDB database with the desired file paths specified through the @DataFilePath and @LogFilePath parameters.

The above is the detailed content of How to Create a SQL Database with Dynamically Parameterized File Paths?. For more information, please follow other related articles on the PHP Chinese website!