CollectionSummary-DynamicParam.ps1

$RBAC_CollectionQuery = @"
declare @SID varbinary(39), @Token nvarchar(max), @UserID nvarchar(max)
SET @SID = SUSER_SID()
Set @Token = 'S-1-5-21-'
+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,13,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,17,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,21,4),1)) as varbinary(4)) as bigint) as varchar(10))
+'-'+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),'0x'+sys.fn_varbintohexsubstring(0,@SID,25,4),1)) as varbinary(4)) as bigint) as varchar(10))
set @UserID = dbo.fn_rbac_GetAdminIDsfromUserSIDs(@Token)
Select
        cols.SiteID AS CollectionID, cols.CollectionName, cols.LimitToCollectionID, cols.LimitToCollectionName
From
        dbo.fn_rbac_Collections(@UserID) cols
order by
        cols.CollectionName
"@
        $CollectionInfo = Invoke-DBAQuery -SqlInstance $SQLServer -Database $Database -Query $RBAC_CollectionQuery
        #endregion RBAC enforced query to retrieve CollectionID and CollectionName

        #region Generate dynamic parameters
        $Position = 2
        foreach ($Var in @('CollectionID', 'CollectionName', 'LimitToCollectionID', 'LimitToCollectionName')) {
            $Set = $(@($CollectionInfo.$Var) | Where-Object { if ($null -ne $PSItem) {
                        $PSItem.ToString().Trim()
                    } } | Select-Object -Unique)
        $newDynamicParamSplat = @{
            Position         = $Position++
            HelpMessage      = "Filter collection summary results by the $Var field"
            ParameterSetName = [string]::Format('FilterBy{0}', $Var)
            DPDictionary     = $Dictionary
            Mandatory        = $true
            Type             = [string[]]
            ValidateSet      = $Set
            Name             = $Var
        }
        New-DynamicParam @newDynamicParamSplat
    }