Gets an array of objects representing the return value of a stored procedure.

GetStoredProcedureResult.ps1

Function GetStoredProcedureResult
{
    # Gets an array of objects representing the return value of the
    # specified stored procedure. This function allows up to one
    # (optional) named SQL integer parameter to be provided.

    Param (
        [string]$Server = $(Throw "No SQL server specified."),
        [string]$Database = $(Throw "No database name specified."),
        [string]$StoredProcedure = $(Throw "No stored procedure name specified."),
        [string]$ParameterName,
        [int]$ParameterValue
    )
    
    $connection = New-Object System.Data.SqlClient.SQLConnection
    $connection.ConnectionString = "Data Source=$Server;Integrated Security=SSPI;Initial Catalog=$Database"

    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection = $connection
    $cmd.CommandText = $StoredProcedure
    $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
    
    If ($ParameterName -and $ParameterValue)
    {
        $cmd.Parameters.AddWithValue($ParameterName, $ParameterValue)
    }
    
    $connection.Open();
    $reader = $cmd.ExecuteReader()

    $result = @()
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
            $row[$reader.GetName($i)] = $reader.GetValue($i)
        }
        
        $result += New-Object psobject -property $row            
    }

    $connection.Close();
    $result
}