Getting a dynamic database parameter on a SQL server

Question

I'm creating a PS function with 2 parameters: $server & $database. I need the $database parameter to be auto-populated (dynamic validation set), depending on the first parameter ($server)

I got most of the code from here

However it is NOT working. What am I doing wrong here? Any insight is greatly appreciated. Thank you.

function Get-databases {
    [CmdletBinding()]
    Param(
        # Any other parameters can go here              

        [Parameter(Mandatory)][string] $Server 

    )

    DynamicParam {
            # Set the dynamic parameters' name
            $ParameterName = 'Database'

            # Create the dictionary 
            $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary

            # Create the collection of attributes
            $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]

            # Create and set the parameters' attributes
            $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
            $ParameterAttribute.Mandatory = $true
            $ParameterAttribute.Position = 1

            # Add the attributes to the attributes collection
            $AttributeCollection.Add($ParameterAttribute)

            # Generate and set the ValidateSet             
            $arrSet = (Invoke-Sqlcmd -ServerInstance $server  -query 'select name from sys.databases order by 1'   -ConnectionTimeout 60 -QueryTimeout 99999).name                         
            $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($arrSet)

            # Add the ValidateSet to the attributes collection
            $AttributeCollection.Add($ValidateSetAttribute)

            # Create and return the dynamic parameter
            $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
            $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
            return $RuntimeParameterDictionary
    }

    begin {
        # Bind the parameter to a friendly variable
        $db = $PsBoundParameters[$ParameterName]
    }

    process {
        # Your code goes here
        $db 

    }

}

Answer 1

If you have Invoke-SqlCmd in DynamicParam ValidateSet attribute, your tab completion is going to execute the whole Invoke-SqlCmd for validation , this is very expensive w.r.t performance.

You can give some xyz value to -DataBase without using Tab completion, you will see it validating the Input, but will take little time as it will execute the Invoke-SqlCmd for the validation .

So I would advice not to go with DynamicParams or to Avoid Validation within DynamicParam, you can have an explicit validation in Begin block.