$PSBoundParameters is a function-local PowerShell hashtable containing only parameters explicitly supplied by the caller and their values. It supports dynamic SQL filtering by distinguishing omitted filters from supplied ones. section 7.4.1
Use it to construct a parameterized WHERE clause:
- Enumerate the hashtable with GetEnumerator(). section 7.4.1
- Add a column filter only when its parameter was supplied. section 7.4.1
- Join multiple filters with -and, while allowing no WHERE clause. section 7.4.1
- Exclude common parameters added by CmdletBinding, such as Verbose. section 7.4.1
Dynamic server filtering
Get-PoshServer can inspect supplied filters such as -Name, -Source, and -Status, build the SQL WHERE clause, and pass matching values through a parameter hashtable. section 7.4.1
Listing 7.7 Get-PoshServer
Function Get-PoshServer {
[CmdletBinding()]
[OutputType([object])]
param(
[Parameter(Mandatory = $false)]
[int]$ID,
[Parameter(Mandatory = $false)]
[string]$Name,
[Parameter(Mandatory = $false)]
[string]$OSType,
[Parameter(Mandatory = $false)]
[string]$OSVersion,
[Parameter(Mandatory = $false)]
[string]$Status,
[Parameter(Mandatory = $false)]
[string]$RemoteMethod,
[Parameter(Mandatory = $false)]
[string]$UUID,
[Parameter(Mandatory = $false)]
[string]$Source,
[Parameter(Mandatory = $false)]
[string]$SourceInstance
)
[System.Collections.Generic.List[string]] $where = @()
$SqlParameter = @{}
$PSBoundParameters.GetEnumerator() | #1
Where-Object { $_.Key -notin
[System.Management.Automation.Cmdlet]::CommonParameters } |
ForEach-Object {
$where.Add("$($_.Key) = @$($_.Key)")
$SqlParameter.Add($_.Key, $_.Value)
}
$Query = "SELECT * FROM " + #2
$_PoshAssetMgmt.ServerTable
if ($where.Count -gt 0) { #3
$Query += " Where " + ($where -join (' and '))
}
Write-Verbose $Query
$DbaQuery = @{
SqlInstance = $_SqlInstance
Database = $_PoshAssetMgmt.Database
Query = $Query
SqlParameter = $SqlParameter
}
Invoke-DbaQuery @DbaQuery #4
}
Inspect the complete Get-PoshServer implementation, including parameter declarations, enumeration, common-parameter filtering, and SQL parameter construction. section 7.4.1
