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.

Solution

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%)

Comments

[marc_s]

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

[Jeremy]

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]

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

[Jeremy]

I think I figured it out: The sqlcmd statement should be like so: sqlcmd -v varMDF="N'C:\Dashboard\WHONET.mdf'" varLDF="N'C:\Dashboard\WHONET_log.ldf'". I had to put the path names between N' and '.

[Jeremy]

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

[Joe Stefanelli]

@Jeremy: Sorry for that goof. I edited my answer to include your correction.

[Jeremy]

@Joe: No problem. I missed it too.

[labilbe]

Interesting to use it in combination with %~dp0

[Richard.Gale]

Can SIZE be passed as a parameter also?

[BitLauncher]

You can even define default values for the scripting variables by setting (user/system) environment variables or in the console e. g. SET varMDF=C:\dev\SAMPLE.mdf, before calling sqlcmd. Then you do can skip the scripting variables' values at calling sqlcmd without getting errors. But if you provide them, its values are used in the sql file and not the values of the environment variables anymore. Details here