How to Pass a Parameter to a T-SQL Script

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

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

How to pass parameters to sql scripts via command line

The parameters that are being passed from the command line are available in SQLPLUS as &1 and &2.

select * from table
where create_date between &1 and &2;

To prevent problems with date formatting you may want to consider changing that to

select * from table
where create_date between to_date('&1','DD-MM-YYYY') and to_date('&2','DD-MM-YYYY');

Or whatever date format you want to use.

How to pass parameters to SQL script via Powershell

Just in case someone else needs to do this... here is a working example.

Power Shell Script:

sqlcmd -S uk-ldn-dt270 -U sa -P 1NetNasdf£! -v db = "'DatabaseNameHere'" -i $scriptFile -b | Tee-Object -filepath $sqlLog

Note the -v switch to assign the variables

And here is the MS SQL:

USE MASTER
GO

if db_id($(db)) is null

BEGIN
    EXEC('
    RESTORE DATABASE ' + $(db) + '
    FROM DISK = ''D:\DB Backup\EmptyLiveV5.bak''
    WITH MOVE ''LiveV5_Data'' TO ''C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LiveV5_' + $(db) + '.MDF'',
    MOVE ''LiveV5_Log'' To ''C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LiveV5_' + $(db) + '_log.LDF'', REPLACE,
    STATS =10')
END

Note: You do not have to assign the scripting varible to a normal sql varible like this.

SET @dbName = $(db)

you can just use it in your sql code. - Happy coding.

Pass a variable from one sql script to another as parameter

Getting values from a variable to a DEFINE is a bit of a chore and requires a convoluted process.
Create a bind variable in SQL*Plus and do whatever you want to get a value into that bind variable.
Now, use the COLUMN command with NEW_VALUE to define a column and a define variable that it will go into.
Then use a SELECT to get the bind variable into the new define variable.

You may wish to add some SQL*Plus tweaks to minimize the output from some of these activities.

Here's a go at it:

script.sql

set serveroutput on size unlimited

-- use a bind variable defined at SQL*Plus level for this
variable v_MY_PARAM VARCHAR2(32)

BEGIN
    -- can use SELECT to do this too
    :v_MY_PARAM := 'test';
END;
/

-- Here is the magic that converts a bind variable to a DEFINE variable
column mycol new_value myvar
select :v_MY_PARAM as mycol from dual;

BEGIN
    @mysqlscript.sql &myvar
END;
/

mysqlscript.sql

dbms_output.put_line('Working? &1');
-- But wait!!!
dbms_output.put_line('This works too: ' || :v_MY_PARAM);

The first option uses a proper argument, handled as a SQL*Plus define, swapped in verbatim.

The second option uses the bind variable directly in your second file. The bind variable is global to SQL*Plus and can be seen by all scripts. The down side of this second approach is that your second script needs to know the name of the bind variable, and will likely be too closely coupled with the caller. Better to use the command-line argument approach outlined above.

---

Related Topics