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.

Answer 1

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

Answer 2

Use the environment variables.

Setting up variable in the .bat file:

set myVariable=C:\SQL\Data

Reading variable from the .sql file:

select '($myVariable)'