How can I use parameter in a SQL script file

Question

I want to create a database with a SQL script file but I cannot give a parameter instead of database name how can I solve this?

Declare @Test as nvarchar(max)

CREATE DATABASE [@Test] ON  PRIMARY 
  ( NAME = N'@Test', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\@Test.mdf', 
    SIZE = 11264KB , MAXSIZE = 51200KB , FILEGROWTH = 10%)
 LOG ON 
 ( NAME = N'@Test_log', 
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\@Test_1.ldf' , 
   SIZE = 9216KB , MAXSIZE = 25600KB , FILEGROWTH = 1024KB )
GO

ALTER DATABASE [@Test] SET COMPATIBILITY_LEVEL = 100
GO

How are you planning to use this parameter? You are declaring it, but you're not giving it a value. — Prutswonder, Feb 24 '12 at 10:18
possible duplicate of [How to pass in parameters to a SQL Server script called with sqlcmd?](http://stackoverflow.com/questions/3814302/how-to-pass-in-parameters-to-a-sql-server-script-called-with-sqlcmd) — gbn, Feb 24 '12 at 10:26
And how are you running this in C#? Can you include a sample of your code in your question? — Prutswonder, Feb 24 '12 at 10:28

marc_s · Answer 1 · 2012-02-24T10:30:44.330

If you use SQLCMD compatibility mode in SQL Server Management Studio, you can use SQLCMD parameters:

:setvar dbname YourDatabase

CREATE DATABASE $(dbname) ON  PRIMARY 
( NAME = $(dbname), 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$(dbname).mdf', 
  SIZE = 11264KB , MAXSIZE = 51200KB , FILEGROWTH = 10%)
LOG ON 
 ( NAME = N'$(dbname)_log', 
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$(dbname)_log.ldf' , 
   SIZE = 9216KB , MAXSIZE = 25600KB , FILEGROWTH = 1024KB )
GO

ALTER DATABASE $(dbname) SET COMPATIBILITY_LEVEL = 100
GO

Those parameters can also be specified using the SQLCMD command line tool on the command line

You can enable the "SQLCMD mode" in SQL Server Management Studio under Tools > Options

enter image description here

And that change only affects new windows in SSMS being opened - it does not apply to already open windows.

How can I use parameter in a SQL script file

1 Answers1