internal/functions/Get-SqlServerUpdate.ps1

function Get-SqlServerUpdate {
    <#
    Originally based on https://github.com/adbertram/PSSqlUpdater
    Internal function. Provides information on the update path for a specific set of SQL Server instances based on current and target SQL Server levels.
    Component parameter is using the output object of Get-SqlInstanceComponent.
    #>

    [CmdletBinding(DefaultParameterSetName = 'Latest')]
    param
    (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [DbaInstanceParameter]$ComputerName,
        [pscredential]$Credential,
        [Parameter(Mandatory, ParameterSetName = 'Latest')]
        [ValidateSet('ServicePack', 'CumulativeUpdate')]
        [string]$Type,
        [object[]]$Component,
        [Parameter(ParameterSetName = 'Number')]
        [int]$ServicePack,
        [Parameter(ParameterSetName = 'Number')]
        [int]$CumulativeUpdate,
        [Parameter(Mandatory, ParameterSetName = 'KB')]
        [ValidateNotNullOrEmpty()]
        [string]$KB,
        [bool]$Restart,
        [string]$InstanceName,
        [string[]]$Path,
        [bool]$EnableException = $EnableException,
        [bool]$Continue
    )
    process {

        # check if any type of the update was specified
        if ($PSCmdlet.ParameterSetName -eq 'Number' -and -not ((Test-Bound ServicePack) -or (Test-Bound CumulativeUpdate))) {
            Stop-Function -Message "No update was specified, provide at least one value for either SP/CU"
            return
        }
        $computer = $ComputerName.ComputerName
        $verCount = ($Component | Measure-Object).Count
        $verDesc = ($Component | Foreach-Object { "$($_.Version.NameLevel) ($($_.Version.Build))" }) -join ', '
        Write-Message -Level Debug -Message "Selected $verCount existing SQL Server version(s): $verDesc"

        # Group by version
        $currentVersionGroups = $Component | Group-Object -Property { $_.Version.NameLevel }
        #Check if more than one version is found
        if (($currentVersionGroups | Measure-Object ).Count -gt 1 -and ($CumulativeUpdate -or $ServicePack) -and !$MajorVersion) {
            Stop-Function -Message "Updating multiple different versions of SQL Server to a specific SP/CU is not supported. Please specify a version of SQL Server on $computer that you want to update."
            return
        }
        ## Find the architecture of the computer
        if ($arch = (Get-DbaCmObject -ComputerName $computer -ClassName 'Win32_ComputerSystem').SystemType) {
            if ($arch -eq 'x64-based PC') {
                $arch = 'x64'
            } else {
                $arch = 'x86'
            }
        } else {
            Write-Message -Level Warning -Message "Failed to determine the arch of $computer, using x64 by default"
            $arch = 'x64'
        }
        $targetLevel = ''
        # Launch a setup sequence for each version found
        foreach ($currentGroup in $currentVersionGroups) {
            $currentMajorVersion = "SQL" + $currentGroup.Name
            $currentMajorNumber = $currentGroup.Name

            # Use the earliest version in case specifics are needed
            $currentVersion = $currentGroup.Group | Sort-Object -Property { $_.Version.BuildLevel } | Select-Object -ExpandProperty Version -First 1

            #create output object
            $output = [pscustomobject]@{
                ComputerName  = $ComputerName
                MajorVersion  = $currentMajorNumber
                Build         = $currentVersion.Build
                TargetVersion = $null
                TargetLevel   = $null
                KB            = $null
                Successful    = $false
                Restarted     = $false
                InstanceName  = $InstanceName
                Installer     = $null
                ExtractPath   = $null
                Notes         = $null
                ExitCode      = $null
                Log           = $null
            }

            # create a parameter set for Find-SqlServerUpdate
            $kbLookupParams = @{
                Architecture = $arch
                MajorVersion = $currentGroup.Name
                Path         = $Path
            }
            # Find target KB number based on provided SP/CU levels or KB numbers
            if ($CumulativeUpdate -gt 0) {
                #Cumulative update is present - installing CU
                if (Test-Bound -Parameter ServicePack) {
                    #Service pack is present - using it as a reference
                    $targetKB = Get-DbaBuildReference -MajorVersion $currentMajorNumber -ServicePack $ServicePack -CumulativeUpdate $CumulativeUpdate
                } else {
                    #Service pack not present - using current SP level
                    $targetSP = $currentVersion.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1
                    $targetKB = Get-DbaBuildReference -MajorVersion $currentMajorNumber -ServicePack $targetSP -CumulativeUpdate $CumulativeUpdate
                }
            } elseif ($ServicePack -gt 0) {
                #Service pack number was passed without CU - installing service pack
                $targetKB = Get-DbaBuildReference -MajorVersion $currentMajorNumber -ServicePack $ServicePack
            } elseif ($KB) {
                $targetKB = Get-DbaBuildReference -KB $KB
                if ($targetKB -and $currentMajorNumber -ne $targetKB.NameLevel) {
                    Write-Message -Level Debug -Message "$($targetKB.NameLevel) is not a target Major version $($currentMajorNumber), skipping"
                    continue
                }
            } else {
                #No parameters = latest patch. Find latest SQL Server build and corresponding SP and CU KBs
                $latestCU = Test-DbaBuild -Build $currentVersion.BuildLevel -MaxBehind '0CU'
                if (!$latestCU.Compliant) {
                    #more recent build is found, get KB number depending on what is the current upgrade $Type
                    $targetKB = Get-DbaBuildReference -Build $latestCU.BuildTarget
                    $targetSP = $targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1
                    if ($Type -eq 'CumulativeUpdate') {
                        if ($currentVersion.SPLevel -notcontains 'LATEST') {
                            $currentSP = $currentVersion.SPLevel | Where-Object { $_ -ne 'LATEST' } | Select-Object -First 1
                            Stop-Function -Message "Current SP version $currentMajorVersion$currentSP is not the latest available. Make sure to upgade to latest SP level before applying latest CU." -Continue
                        }
                        $targetLevel = "$($targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' })$($targetKB.CULevel)"
                        Write-Message -Level Debug -Message "Found a latest Cumulative Update $targetLevel (KB$($targetKB.KBLevel))"
                    } elseif ($Type -eq 'ServicePack') {
                        $targetKB = Get-DbaBuildReference -MajorVersion $targetKB.NameLevel -ServicePack $targetSP
                        $targetLevel = $targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' }
                        if ($currentVersion.SPLevel -contains 'LATEST') {
                            Write-Message -Message "No need to update $currentMajorVersion to $targetLevel - it's already on the latest SP version" -Level Verbose
                            continue
                        }
                        Write-Message -Level Debug -Message "Found a latest Service Pack $targetLevel (KB$($targetKB.KBLevel))"
                    }
                } else {
                    Write-Message -Message "$($currentVersion.Build) on computer [$($computer)] is already the latest available." -Level Verbose
                    continue
                }
            }
            if ($targetKB.KBLevel) {
                if ($targetKB.MatchType -ne 'Exact') {
                    Stop-Function -Message "Couldn't find an exact build match with specified parameters while updating $currentMajorVersion" -Continue
                }
                $output.TargetVersion = $targetKB
                $targetLevel = "$($targetKB.SPLevel | Where-Object { $_ -ne 'LATEST' })$($targetKB.CULevel)"
                $targetKBLevel = $targetKB.KBLevel | Select-Object -First 1
                Write-Message -Level Verbose -Message "Upgrading SQL$($targetKB.NameLevel) to $targetLevel (KB$($targetKBLevel))"
                $kbLookupParams.KB = $targetKBLevel
            } else {
                $output.Notes = "Could not find a KB$KB reference for $currentMajorVersion SP $ServicePack CU $CumulativeUpdate"
                $output
                Stop-Function -Message $output.Notes -Continue
            }

            # Compare versions - whether to proceed with the installation
            $needsUpgrade = $false
            foreach ($currentComponent in $currentGroup.Group) {
                $groupVersion = $currentComponent.Version
                #target version is less than requested
                if ($groupVersion.BuildLevel -lt $targetKB.BuildLevel) {
                    $needsUpgrade = $true
                }
                #target version is the same but installation has failed last time
                elseif ($groupVersion.BuildLevel -eq $targetKB.BuildLevel -and $Continue -and $currentComponent.Resume) {
                    $needsUpgrade = $true
                }
            }
            if (!$needsUpgrade) {
                Write-Message -Message "Current $currentMajorVersion version $($currentVersion.BuildLevel) on computer [$($computer)] matches or already higher than target version $($targetKB.BuildLevel)" -Level Verbose
                continue
            }

            $output.TargetLevel = $targetLevel
            $output.KB = $kbLookupParams.KB
            ## Find the installer to use

            $installer = Find-SqlServerUpdate @kbLookupParams
            if (!$installer) {
                $output.Notes = "Could not find installer for the $currentMajorVersion update KB$($kbLookupParams.KB)"
                $output
                Stop-Function -Message $output.Notes -Continue
            }
            $output.Installer = $installer.FullName
            $output.Successful = $true
            #Return the object for further processing
            $output
        }
    }
}