SQL Server – Using PowerShell Variables in a File-Stored Query

powershellsql serversql-server-2012

I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell script, which would make what I'm trying to do trivial. What I need is to be able to have SQL Server do is to access the Powershell variable and use it in the query I'm sending. xp_cmdshell is coming up as a potential option to do this with, but I'd sooner not expose myself to the security vulnerabilities there if I don't have to.

Here's some pseudocode to get my idea across easier:

PSScript.ps1:

$var = 1
$query = query.sql
$con = connection(server_connection_params)
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
$command.ExecuteScalar()

Query.sql:

SELECT * FROM table WHERE field = $var;

Is there any way I could access $var in this fashion given how I'm calling the SQL in Powershell?

Best Answer

I expect the sample query you gave is basic, so my offer is basic but I think should work for you even with complex script files that require multiple variables to be passed/replaced.

The key here is you need to have a place holder in your script files that you can easily search for in PowerShell, then just replace it with that value.

An example query:

SELECT * FROM sys.databases WHERE name = 'var1'

In this example I am going to pull that query in from a file and then search for the var1 value, replacing it with the database name I want to query for...

$var = 'MyDatabase'
$query = Get-Content .\Sample.sql | foreach {$_.replace("var1",$var)}

Example usage:

enter image description here

Related Question