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
– Prutswonder, Commented Feb 24, 2012 at 10:18
possible duplicate of How to pass in parameters to a SQL Server script called with sqlcmd? — gbn
– gbn, Commented Feb 24, 2012 at 10:26
And how are you running this in C#? Can you include a sample of your code in your question? — Prutswonder
– Prutswonder, Commented Feb 24, 2012 at 10:28

marc_s · Accepted Answer · 2012-02-24 10:30:44Z

3

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.

edited Feb 24, 2012 at 10:30

answered Feb 24, 2012 at 10:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I use parameter in a SQL script file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related