Functions/Invoke-SqlScalar.ps1
|
<#
.SYNOPSIS Executes a SQL query and returns the first column of the first row. .DESCRIPTION The Invoke-SqlScalar function executes a SQL query against a database and returns the scalar result (first column of the first row). This is useful for queries that return a single value, such as COUNT queries or simple lookups. .PARAMETER TargetServer Specifies the target SQL Server name or instance. .PARAMETER DatabaseName Specifies the target database name. .PARAMETER TargetUser Optional. Specifies the username for SQL Server authentication. If not provided, integrated security will be used. .PARAMETER TargetPasswordSecure Optional. Specifies the secure password for SQL Server authentication. Used in conjunction with TargetUser. .PARAMETER Query Specifies the SQL query to execute. Should be a query that returns a scalar value. .OUTPUTS Object Returns the scalar result from the query execution. .EXAMPLE Invoke-SqlScalar -TargetServer "localhost" -DatabaseName "AdventureWorks" -Query "SELECT COUNT(*) FROM Person.Person" Returns the count of records in the Person.Person table using integrated security. .EXAMPLE $securePassword = "P@ssw0rd" | ConvertTo-SecureString -AsPlainText -Force Invoke-SqlScalar -TargetServer "sqlserver.example.com" -DatabaseName "MyDB" -TargetUser "dbuser" -TargetPasswordSecure $securePassword -Query "SELECT MAX(ID) FROM Orders" Returns the maximum ID from the Orders table using SQL Server authentication. .EXAMPLE $lastDeployDate = Invoke-SqlScalar -TargetServer "localhost" -DatabaseName "MyApp" -Query "SELECT TOP 1 DeployDate FROM DeploymentLog ORDER BY DeployDate DESC" Gets the most recent deployment date from a deployment log table. .NOTES This function automatically handles connection management (opening and closing connections). For queries that return multiple rows or columns, consider using other SQL execution methods. The function supports both Windows Authentication (integrated security) and SQL Server authentication. #> Function Invoke-SqlScalar { [cmdletbinding()] param( [Parameter(Mandatory = $true, HelpMessage = "Target SQL Server name")] [string] $TargetServer, [Parameter(Mandatory = $true, HelpMessage = "Target database name")] [string] $DatabaseName, [Parameter(HelpMessage = "Username for SQL Server authentication")] [string] $TargetUser, [Parameter(HelpMessage = "Secure password for SQL Server authentication")] [securestring] $TargetPasswordSecure, [Parameter(Mandatory = $true, HelpMessage = "SQL query to execute")] [string] $Query ) $Params = @{DatabaseName = $DatabaseName; TargetServer = $TargetServer } if (-not [String]::IsNullOrWhiteSpace($TargetUser)) { $params.TargetUser = $TargetUser; $params.TargetPassword = $TargetPasswordSecure; } $conString = Get-ConnectionString @Params $Con = New-SqlConnection -ConnectionString $conString try { $result = Invoke-SqlScalarInternal -Connection $con -Query $Query return $result } finally { Close-SqlConnection $con } } |