Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share

migration rejected from stackoverflow.com Jul 18 '13 at 12:44

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Aaron Bertrand Jul 18 '13 at 12:44

If this question can be reworded to fit the rules in the help center, please edit the question.

    
This question appears to be off-topic because it was asked and answered long before dba.SE existed, and was migrated incorrectly. –  Aaron Bertrand Jul 18 '13 at 12:44

2 Answers 2

up vote 15 down vote accepted

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%)
share
locked by Community Jul 18 '13 at 12:44
4  
In that case, you probably also want to define a variable $(DBName) for the database name itself .... –  marc_s Sep 28 '10 at 15:29
    
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 Sep 28 '10 at 15:46
    
I'm trying various things, such as escaping the slashes or the quotes. Any ideas how to fix those errors? –  Jeremy Sep 28 '10 at 15:47
    
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 Sep 28 '10 at 15:53
    
@marc_s: That is a good idea. Thanks. –  Jeremy Sep 28 '10 at 15:54

Use the environment variables.

Setting up variable in the .bat file:

set myVariable=C:\SQL\Data

Reading variable from the .sql file:

select '($myVariable)'
share
    
The question was originally asked on StackOverflow and has been re-opened there. I suggest you post your answer there as well, as it is unclear what will ultimately happen to this copy. –  Aaron Bertrand Jul 18 '13 at 13:20