39

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeremy
  • 3,221
  • 7
  • 27
  • 31

1 Answers1

54

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%)
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 6
    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
  • 1
    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
  • 2
    Actually a better method is to add the N'' in the SQL script itself: FILENAME = N'$(varMDF)' ... FILENAME = N'$(varLDF)' – Jeremy Sep 28 '10 at 15:57
  • @Jeremy: Sorry for that goof. I edited my answer to include your correction. – Joe Stefanelli Sep 28 '10 at 16:07
  • @Joe: No problem. I missed it too. – Jeremy Sep 28 '10 at 16:10
  • 1
    Interesting to use it in combination with %~dp0 – labilbe Oct 02 '15 at 12:54
  • Can SIZE be passed as a parameter also? – Richard Gale Oct 10 '18 at 09:44
  • 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](https://learn.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15) – BitLauncher Jul 10 '20 at 05:57
  • And what if you only want to use variable up to a certain point in the sql script? Because I have inserts containing $(...) which must not be interpreted as variables. Can you turn off variables in the script after some point? – Jan Jun 09 '23 at 09:07