Using a PoweShell parameter value in a SQL query

PowerShell Help
1

Hey guys,

I am trying to run a remote query against a MSSQL database and want to use a user provided parameter value as part of the query; e.g., CmdLet asks for a document number when CmdLet executed and that value is in the WHERE clause. I was hoping there was something like the $Using feature but I have not found anything.

Thanks in advance.

Function Search-TestDocumentNumber {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
        [String]$SqlServer,
        [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
        [String]$Database,
        [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
        $DocNumber
    )
    BEGIN {
        [int]$ConnectionTimeout = 30
        [int]$QueryTimeout = 120
        $TSQL = @'
        SELECT * FROM myTable WHERE docnum = $DocNumber
'@
        $SqlConnection = new-object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
        $cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
        $cmd.CommandTimeout = $QueryTimeout
    }
    PROCESS {
        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        $Results = $ds.Tables[0].Rows
    }
    END {
        $SqlConnection.Close()
        $Results | Format-Table -AutoSize
    }
}
2

I think your real variable needs to be mafe with @" … "@ in order for the variable to be evaluated. Once you make that change, I think you’ll have to put $docnumber like so, ‘$docnumber’.

Let me know how that works for you.

3

That seems to do the trick. Obviously I was thinking single quotes do not expand variable values so didn’t try this. Looks to be a SQL thing.

Thank you.

Updated code for reference:

Function Search-TestDocumentNumber {
        [CmdletBinding()]
        param(
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            [String]$SqlServer,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            [String]$Database,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            $DocNumber
        )
        BEGIN {
            [int]$ConnectionTimeout = 30
            [int]$QueryTimeout = 120
            $TSQL = @"
            SELECT * FROM myTable WHERE docnum = '$DocNumber'
"@
            $SqlConnection = new-object System.Data.SqlClient.SQLConnection
            $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SqlServer, $Database, $ConnectionTimeout
            $SqlConnection.ConnectionString = $ConnectionString
            $SqlConnection.Open()
            $cmd = new-object system.Data.SqlClient.SqlCommand($Tsql, $SqlConnection)
            $cmd.CommandTimeout = $QueryTimeout
        }
        PROCESS {
            $ds = New-Object system.Data.DataSet
            $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
            [void]$da.fill($ds)
            $Results = $ds.Tables[0].Rows
        }
        END {
            $SqlConnection.Close()
            $Results | Format-Table -AutoSize
        }
}
4

Glad that worked for you!

5

I would recommend using TSQL params instead in order to avoid SQL injection and I think it simplifies the overall since your $TSQL would change to:

$TSQL = "SELECT * FROM myTable WHERE docnum = @docnum"

And then your SQLCommand would be formed as

$cmd.Parameters.AddWithValue("@docnum", $Doc number)

Between lines 21 and 22