How to pass in parameters to a SQL Server script called with sqlcmd?

Question

Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows:

CREATE DATABASE [SAMPLE] ON  PRIMARY 
( NAME = N'SAMPLE', FILENAME = N'c:\dev\SAMPLE.mdf' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SAMPLE_log', FILENAME = N'c:\dev\SAMPLE_log.ldf' , SIZE = 29504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

I want to be able to pass in the filenames for the database and the log so that I don't have to hardcode 'C:\dev\SAMPLE.mdf' and 'C:\dev\SAMPLE_log.ldf'.

Is there a way to do this? I am running Microsoft SQL Server 2008 Express. Let me know if you need any more information.

Joe Stefanelli · Accepted Answer · 2010-09-28 16:06:36Z

60

Use the -v switch to pass in variables.

sqlcmd -v varMDF="C:\dev\SAMPLE.mdf" varLDF="C:\dev\SAMPLE_log.ldf"

Then in your script file

CREATE DATABASE [SAMPLE] ON  PRIMARY 
( NAME = N'SAMPLE', FILENAME = N'$(varMDF)' , SIZE = 23552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SAMPLE_log', FILENAME = N'$(varLDF)' , SIZE = 29504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

edited Sep 28, 2010 at 16:06

answered Sep 28, 2010 at 15:28

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

11 Comments

marc_s Over a year ago

In that case, you probably also want to define a variable $(DBName) for the database name itself ....

Jeremy Over a year ago

I am getting errors when I run it: Incorrect syntax near 'C:'. The label 'C' has already been declared. Label names must be unique within a query batch or stored procedure.

Jeremy Over a year ago

I'm trying various things, such as escaping the slashes or the quotes. Any ideas how to fix those errors?

Jeremy Over a year ago

Actually a better method is to add the N'' in the SQL script itself: FILENAME = N'$(varMDF)' ... FILENAME = N'$(varLDF)'

labilbe Over a year ago

Interesting to use it in combination with %~dp0

|

Collectives™ on Stack Overflow

How to pass in parameters to a SQL Server script called with sqlcmd?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related