How to use variable in SQL from powershell script

Question

I am trying to run a SQL from Power Shell(which is on my windows 7 64 bit desktop) and the remote database host is MS SQL Server 2012.

The code is:

$var1 = 'string';

function Get-ODBC-Data{
   param(
   [string]$query=$('
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = ''$var1''
                                ;
'),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

 $result = Get-ODBC-Data

Write-Host "SQL_Output: " $result[0];

If I use 'string' in the SQL's where clause instead of $var1 then te script works fine and gives expected result.

Quetion But I want to be able to pass any string as $var1 to the script as parameter. Then use it in the where clause of the SQL. How can I achieve this?

What I tried I have tried to enclose $var1 in 1,2 or 3 single quotes in the where clause in attempt to escape the single quote. Also tried adding/removing single quote from 'string' when $var1 is assigned value. I did try [string]$var1 = 'string' as well but none of these worked and I keep getting error mostly related to SQL syntax.

Answer 1

Try this:

function Get-ODBC-Data{
   param(
   [string]$query=$("
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;
"),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "DRIVER={SQL Server};Server=123.456.78.90;Initial Catalog=master;Uid=$username;Pwd=$password;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

 $result = Get-ODBC-Data

Write-Host "SQL_Output: " $result[0];

The following runs fine on my setup, and shows the correct results:

$var1 = "test22"

function Get-ODBC-Data{
   param(
   [string]$query=$("
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;
"),
   [string]$username='db_user_name',
   [string]$password='db_password'
   )

   return $query

}

$result = Get-ODBC-Data

Write-Host " ################### Query ######################## "
Write-Host $result

However, you may have a much easier time just passing the entire query into the function as a parameter rather than just one variable part of the query.

Or setting it inside the function and passing $var1 as a mandatory parameter like so:

function Get-ODBC-Data{
   param(
   [parameter(Mandatory=$true)][string]$var1,
   [string]$username='db_user_name',
   [string]$password='db_password'
   )
   $query="
                SELECT COUNT(*)
                FROM [master].[sys].[table_name]                
                                WHERE col2 = '$($var1)'
                                ;"

   return $query

}

$result = Get-ODBC-Data -var1 "working"

Write-Host " ################### Query ######################## "
Write-Host $result