How Can I Use Parameters to Specify File Paths When Creating a SQL Database?

Creating a Database with Parameters in CREATE DATABASE Statement

If you intend to specify the file paths for the data file and log file using parameters in a SQL script, you will need to employ dynamic SQL. Here's a revised script that uses dynamic SQL to achieve your desired result:

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 NVARCHAR(MAX)
SELECT @sql = 'CREATE DATABASE TestDB ON PRIMARY ( NAME = ''TestDB_Data'', FILENAME = ' + quotename(@DataFilePath) + ') LOG ON ( NAME = ''TestDB_Log'', FILENAME = ' + quotename(@LogFilePath) + ')'

EXEC (@sql)

This script dynamically constructs the CREATE DATABASE statement using the provided parameters (@DataFilePath and @LogFilePath) and then executes the constructed statement. By using dynamic SQL, you can effectively pass parameters into the CREATE DATABASE statement and specify the file paths for the database files.

The above is the detailed content of How Can I Use Parameters to Specify File Paths When Creating a SQL Database?. For more information, please follow other related articles on the PHP Chinese website!