title: How substitute variable in PowerShell SQL query
body: <p>I need to use a variable in my SQL query. This is my script:</p>
  
  <pre><code>function SQLQueryWriteToFile([string]$SQLquery, [string]$extractFile, [string]$facility)
  {
     #create sql connection to connect to the sql DB
     $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
  
     $sqlConnection.ConnectionString = "Server=blah;Database=blah_Test;User ID=blah;Password=blah" 
     $sqlConnection.Open()
  
     #Create a SqlCommand object to define the query
     $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
     $sqlCmd.CommandText = $SQLquery
     $sqlCmd.Connection = $sqlConnection
  
     #create a SqlAdapter that actually does the work and manages everything
     $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     $sqlAdapter.SelectCommand = $sqlCmd
     $sqlAdapter.SelectCommand.CommandTimeout=300  #set timeout for query execution to 5 minutes (60x5=300)
  
     #create an empty dataSet for the query to fill with its results
     $dataSet = New-Object System.Data.DataSet
  
     #execute the query and fill the dataSet (then disconnect)
     $sqlAdapter.Fill($dataSet)
     $sqlConnection.Close()
  
     #dump the data to csv
     $DataSet.Tables[0] | Export-Csv $extractFile #this may not be comma delimited...need to check
  
  } 
  
  #start here
  
  $SQLquery_Privilege = @"
  SELECT *
  
   FROM "blah_Test".dbo.usr_Person_by_Privileges
  
  WHERE 
         Status in ('Active')  
         and Facility = '$[facility]'
         and Last not like ('%test%')
         and Last not like ('%Test%')
  
  --ORDER BY Last
  "@
  
  
  $extractFiles = @("C:\temp\Privileges_H_Bak.csv","C:\temp\Privileges_E_Bak.csv","C:\temp\Privileges_S_Bak.csv")
  $facCode = @("H","E","S")
  
  #Loop through list of files and queries for ProfileLong
  for($i=0; $i -lt ($facCode.Length); $i++) {
     SQLQueryWriteToFile $SQLquery_Privilege $extractFiles[$i] $facCode[$i]
  }
  </code></pre>
  
  <p>I used the debugger and the query passed into the function does have the $facility variable show in it without substitution.  How do I get it to do the variable substitution?  Also, $facility passed into the function has a value.</p>
  
  <p>There are 0 results showing in each extract files.  When I just had each H,E,S individually in the query, and ran the script, it returns a good amount of rows.</p>
  
  <p>I tried looking at <a href="http://www.sql-workbench.net/manual/using-variables.html" rel="nofollow noreferrer">substitute variable powershell sql</a> but I can tell my query is still returning 0 rows even though I think I'm doing what they do.  </p>