How to pass parameters to SQL script via Powershell

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

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.

Adjusting vonPryz's answer to use:

SET @dbName = '$(db)'

means you can pass in the parameter from the command line in a more natural form as

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

The SqlCmd variable still substitutes correctly.

I know this is an old answer but I do have a better way that is much easier if you only need a small amount of data from Powershell (or even a large amount as long as all you want is text), and you work mainly in SQL for your scripting like I do:

1: Start the PowerShell from SQL using xp_cmdshell, and insert the results to a one-column table which allows NULLs e.g:

DECLARE @Results (Line varchar(1000) NULL)
INSERT @Results
EXEC master.dbo.xp_cmdshell '"powershell.exe C:\PowershellScripts\MyScript.ps1 MyParams"'

2: During your PowerShell script, for anything you want to pass back to SQL, simply use "Write-Output", e.g:

Write-Output $returned_data

You can do this as many times as you want. If you have 10,000 values to pass back to SQL, then you could use write-output 10,000 times.

So in the above example once the "MyScript.ps1" PowerShell script finishes running, all of the output will be in the @Results table variable, ready to be used, queried, imported into individual variables, whatever you want really.