How to pass parameters to SQL script via Powershell

Question

I have a Power-shell script that calls a SQL script. This is currently working, but inside my sql script I have some hard coded parameters that I would like to pass to the SQL script via the powershell.

So this is the snip-it from the Power-shell script

function ExecSqlScript([string] $scriptName)
{
$scriptFile = $script:currentDir + $scriptName
$sqlLog = $script:logFileDir + $scriptName + "_{0:yyyyMMdd_HHmmss}.log" -f (Get-Date)
$result = sqlcmd -S uk-ldn-dt270 -U sa -P passwordhere3! -i $scriptFile -b | Tee-Object -             filepath $sqlLog

 if ($result -like "*Msg *, Level *, State *" -Or $result -like "*Sqlcmd: Error:*") 
    {
    throw "SQL script " + $scriptFile + " failed: " + $result
    }   
}

try
{
    ExecSqlScript "restoreDatabase.sql"
}
catch
{
 //Some Error handling here
}

And this is from the SQL

USE MASTER
GO
DECLARE @dbName varchar(255)
SET @dbName = 'HardCodedDatabaseName'

So I want to pass the value for dbName, any ideas?

Answer 1

You could take advantage of sqlcmd's scripting variables. Those can be used in script file and are marked with $(). Like so,

-- Sql script file
use $(db);
select someting from somewhere;

When calling sqlcmd, use the -v parameter to assign variables. Like so,

sqlcmd -S server\instance -E -v db ="MyDatabase" -i s.sql

Edit

Mind the Sql syntax when setting variables. Consider the following script:

DECLARE @dbName varchar(255)
SET @dbName = $(db)
select 'val' = @dbName

As passed to the Sql Server, it looks like so (Profiler helps here):

use master;

DECLARE @dbName varchar(255)
SET @dbName = foo
select 'val' = @dbName

This is, obviously invalid a syntax, as SET @dbName = foo won't make much sense. The value ought to be within single quotes like so,

sqlcmd -S server\instance -E -v db ="'foo'" -i s.sql

Answer 2

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.

Answer 3

Here's a full example using a different PowerShell approach. Here I'm using a specific script to reset local databases for a clean development environment.

## Reset the Database
$resetScript= "C:\ResetSite\resetDatabases.sql"
Write-Host "Resetting the DB - Running $($resetScript)"
$connectionString = "Server = localhost; Database = 'master'; UID = myusername; PWD = mypassword"

# Create variables & params
$sqlCmdVariables = @(
    "Database=$($siteConfig.db_name)",
    "UserName=$($siteConfig.db_username)",
    "UserPassword=$($siteConfig.db_user_password)"
)
$sqlCmdParameters = @{
    InputFile = $resetScript
    QueryTimeout = 1800
    ConnectionString = $connectionString
    Variable = $sqlCmdVariables
}
# Invoke
Invoke-SqlCmd @sqlCmdParameters

The .sql file then uses the parameters passed in, the same way @nmbell mentions.

-- Declare the vars
DECLARE @Database nvarchar(100), @UserName nvarchar(100), @UserPassword nvarchar(100)

-- Set the vars
SET @Database = '$(Database)' -- database name
SET @UserName = '$(UserName)' -- SQL login and database username
SET @UserPassword = '$(UserPassword)' -- login password

... more stuff here.. use the vars like normal

This is partly derived from this blog post but modified slightly to use a file rather than an inline query.