Invoke-DbaQuery executes T-SQL through the dbatools module and returns query results to PowerShell. Supply connection details such as SQL instance, database, and credentials together with the query, commonly by splatting a hashtable of command parameters. section 7.4
$DbaQuery = @{
SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS"
Database = 'PoshAssetMgmt'
Query = 'SELECT * FROM Servers'
}
Invoke-DbaQuery @DbaQuery
A splatted hashtable keeps the SQL instance, database, and query together, making a basic retrieval command easy to reuse or extend. section 7.4
Filter in SQL before results cross into PowerShell. Retrieving every row and then using Where-Object consumes memory and processes records one at a time, whereas SQL can optimize retrieval through query execution plans, indexes, and statistics. section 7.4
$DbaQuery = @{
SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS"
Database = 'PoshAssetMgmt'
Query = 'SELECT * FROM Servers WHERE Name = @name'
SqlParameter = @{name = 'Srv01'}
}
Invoke-DbaQuery @DbaQuery
This parameterized query selects one server by name while passing the value separately through SqlParameter. section 7.4.1
Use SQL parameters rather than constructing query text by concatenating values:
- Declare a SQL variable such as @name in the query and map it to a PowerShell value in a SqlParameter hashtable. section 7.4.1
- Let parameter handling check and convert data types, including DateTime values passed from PowerShell. section 7.4.1
- Escape problematic characters, support reusable query plans, and prevent supplied values from executing as SQL injection code. section 7.4.1
The cmdlet also supports update workflows. Build parameterized SET values and an optional OUTPUT clause, then execute the update for an ID or for each pipeline input object. Collect returned values so callers can compare fields before and after the change. section 7.5.1
Update servers selected from the pipeline
A server retrieval can feed an update operation: select servers with a given SourceInstance, then update each selected server to a new SourceInstance. The update function adds each input ID to the SQL parameters before calling Invoke-DbaQuery. section 7.5.1
