SQL Server: Use Parameter in Create Database

SQL Server: use parameter in CREATE DATABASE

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)

How to use a variable for the database name in T-SQL?

Put the entire script into a template string, with {SERVERNAME} placeholders. Then edit the string using:

SET @SQL_SCRIPT = REPLACE(@TEMPLATE, '{SERVERNAME}', @DBNAME)

and then run it with

EXECUTE (@SQL_SCRIPT)

---

It's hard to believe that, in the course of three years, nobody noticed that my code doesn't work!

You can't EXEC multiple batches. GO is a batch separator, not a T-SQL statement. It's necessary to build three separate strings, and then to EXEC each one after substitution.

I suppose one could do something "clever" by breaking the single template string into multiple rows by splitting on GO; I've done that in ADO.NET code.

And where did I get the word "SERVERNAME" from?

Here's some code that I just tested (and which works):

DECLARE @DBNAME VARCHAR(255)
SET @DBNAME = 'TestDB'

DECLARE @CREATE_TEMPLATE VARCHAR(MAX)
DECLARE @COMPAT_TEMPLATE VARCHAR(MAX)
DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX)

SET @CREATE_TEMPLATE = 'CREATE DATABASE {DBNAME}'
SET @COMPAT_TEMPLATE='ALTER DATABASE {DBNAME} SET COMPATIBILITY_LEVEL = 90'
SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@CREATE_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@COMPAT_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', @DBNAME)
EXECUTE (@SQL_SCRIPT)

SQL Server: How to use a database name as a parameter in a stored procedure?

You have to use dynamic sql:

@DBname = 'sample';
declare @sql nvarchar(max);

set @sql = '
SELECT *
FROM Table1 INNER JOIN
     @DBname.table2
     On Table1.key = Table2.Key';

set @sql = replace(@sql, '@DBname', @DBname);

exec sp_executesql @sql;

Databases in general -- and SQL Server in particular -- do not allow parameters to represent database names, schema names, table names, or column names.

How to use SqlCommand to CREATE DATABASE with parameterized db name?

Parameters are supported for DML operations not DDL operations, there are no execution plans for DDL operations. you will need to use dynamic SQL

DDL = Data Definition Language (create, drop, alter....)

DML = Data Manipulation Language (select, update, delete, insert)

How to parameterize database name in SQL Server

DECLARE @oltp VARCHAR(50)
SET @oltp = 'Employee';

DECLARE @wh VARCHAR(25)
SET @wh =  '_wh';

DECLARE @DatabaseName SYSNAME = @oltp + @wh;
   
IF NOT EXISTS ( SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
    BEGIN
      DECLARE @ErrorMsg VARCHAR(1000);
      SET @ErrorMsg = QUOTENAME(@DatabaseName)  + ' database does not exist';
      RAISERROR(@ErrorMsg , 16 , 2); 
    END
ELSE 
BEGIN
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT * FROM '  
                               + QUOTENAME(@DatabaseName) 
                               + N'..employee_sales_item;';

    EXEC sys.sp_executesql @Sql;
END

Create Database-SQL with name from variable fails

You have to use dynamic SQL. Also, you need to replace text with nvarchar(max) otherwise text concatenation won't work.

Here is full script for you:

DECLARE @DBNAME sysname;
SET @DBNAME = 'MyDataBase'

DECLARE @DBFullName sysname;
SET @DBFullName = 'MySuperDataBase';

DECLARE @DBFileName nvarchar(max);
SET @DBFileName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.mdf';
DECLARE @DBLogName nvarchar(max);
SET @DBLogName = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEVELOPMENT\MSSQL\DATA\' + @DBName + '.ldf';

DECLARE @SQL nvarchar(max);
SET @SQL = N'
  CREATE DATABASE ' + QUOTENAME(@DBNAME) + ' ON PRIMARY
    (NAME = ' + QUOTENAME(@DBFullName + '_Data') + ',
    FILENAME = ' + QUOTENAME(@DBFileName, '''') + ',
    SIZE = 30720KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%)
   LOG ON (
     NAME = ' + QUOTENAME(@DBFullName + '_Log') + ',
     FILENAME = ' + QUOTENAME(@DBLogName, '''') + ',
     SIZE = 2048KB,
     MAXSIZE = UNLIMITED,
     FILEGROWTH = 10%)'

PRINT @SQL

EXEC (@SQL)
GO

Works on my machine! :)

---

Related Topics