How to Dynamically Create a SQL Server Database Using Parameters?

Using Parameters in CREATE DATABASE Statement

In SQL Server, it is possible to dynamically create a database using parameters to specify the location of data and log files. However, your initial approach resulted in an error related to the use of variables in the CREATE DATABASE statement.

To overcome this limitation, you can employ dynamic SQL, which allows you to construct and execute SQL statements programmatically. Here's how you can achieve your objective:

DECLARE @DataFilePath AS NVARCHAR(MAX)
SET @DataFilePath = N'C:\ProgramData\Gemcom\'

DECLARE @LogFilePath AS NVARCHAR(MAX)
SET @LogFilePath = N'C:\ProgramData\Gemcom\'

-- Construct the dynamic SQL statement
SELECT @sql = 'CREATE DATABASE TestDB ON PRIMARY ( NAME = ''TestDB_Data'', FILENAME = ' + quotename(@DataFilePath) + ') LOG ON ( NAME = ''TestDB_Log'', FILENAME = ' + quotename(@LogFilePath) + ')'

-- Execute the dynamic SQL statement
EXEC (@sql)

This approach uses the quotename() function to properly quote the variable values within the dynamic SQL statement. The EXEC command then executes the constructed statement, allowing you to create the database and specify the desired file paths.

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