如何在PowerShell 7.0中终止递归SQL查询并正确展开BOM

问题背景 #

现有包含PartRn（主键）、UsedOnRn的SQL表，需要向下遍历BOM层级查询部件组成。递归查询在SSMS中运行正常，但在PowerShell 7.0中遍历所有PartRn值时递归无法终止，尝试添加条件、限制递归次数均无效。目标是将父部件的Part_Number、Revision与对应子部件信息并排输出至CSV文件。

原代码的核心问题 #

• 硬编码递归根节点：递归CTE里固定写死UsedOnRn = '2'，没有动态传入当前要处理的父部件PartRn，导致每次都查同一个根，根本没实现遍历所有父部件的需求。
• 循环逻辑混乱：外层循环的$initialResult被内层循环重新赋值覆盖，而且每次进内层都清空$Array1和$Array2，外层循环的初始数据直接丢失。
• 递归终止条件错误：CTE递归部分加了Where cte.UsedOnRn = 2，这会把递归限制在根节点下一层，破坏了正常的层级遍历逻辑，甚至可能引发无限循环。

修正后的代码 #

$server = "Server"
$database = "database"
$csvFilePath = "\\server\BOM\BOM_Master_1.csv"

# 获取需要展开BOM的父部件列表（保留原TOP 5逻辑，可按需调整）
$parentParts = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query @"
SELECT TOP 5 [PartRn],[PART_NUMBER],[NAME],[Revision] 
From [NIMS_Eng].[dbo].[vEngParts_Master_Aras]
"@

$combinedResult = @()

foreach ($parent in $parentParts) {
    $parentPartRn = $parent.PartRn
    $parentRevision = $parent.Revision
    $parentPartNumber = $parent.PART_NUMBER

    Write-Host "正在处理父部件: $parentPartNumber (PartRn: $parentPartRn)" -ForegroundColor Yellow

    # 动态生成递归CTE，传入当前父部件的PartRn作为根节点
    $recursiveQuery = @"
    WITH RecursiveCTE AS (
        -- 起始层：当前父部件直接关联的子部件
        SELECT 
            bom.[Part_Number] AS ChildPartNumber,
            bom.UsedOnRn AS ParentPartRn,
            bom.PartRn AS ChildPartRn,
            bom.QtyPerAssy,
            bom.Item,
            bom.Remarks,
            0 AS RecursionLevel
        FROM [NIMS_Eng].[dbo].[vEngBillsOfMaterial_Aras] AS bom
        WHERE bom.UsedOnRn = '$parentPartRn'
        UNION ALL
        -- 递归层：向下遍历子部件的子部件
        SELECT 
            bom.[Part_Number] AS ChildPartNumber,
            bom.UsedOnRn AS ParentPartRn,
            bom.PartRn AS ChildPartRn,
            bom.QtyPerAssy,
            bom.Item,
            bom.Remarks,
            cte.RecursionLevel + 1
        FROM [NIMS_Eng].[dbo].[vEngBillsOfMaterial_Aras] AS bom
        JOIN RecursiveCTE AS cte ON bom.UsedOnRn = cte.ChildPartRn
        -- 可选：添加层级限制，防止数据循环引用导致无限递归，按需调整数值
        WHERE cte.RecursionLevel < 10
    )
    SELECT * FROM RecursiveCTE
"@

    # 执行递归查询，获取当前父部件的所有子部件
    $childParts = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $recursiveQuery

    # 合并父部件与子部件信息
    foreach ($child in $childParts) {
        $combinedResult += [PSCustomObject]@{
            ParentPartNumber = $parentPartNumber
            ParentRevision   = $parentRevision
            ParentPartRn     = $parentPartRn
            ChildPartNumber  = $child.ChildPartNumber
            ChildPartRn      = $child.ChildPartRn
            QtyPerAssy       = $child.QtyPerAssy
            Item             = $child.Item
            Remarks          = $child.Remarks
            RecursionLevel   = $child.RecursionLevel
        }
    }
}

# 导出到CSV，指定UTF8编码避免乱码
$combinedResult | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8
Write-Host "BOM数据已成功导出到: $csvFilePath" -ForegroundColor Green

关键修正说明 #

• 动态绑定父部件：把CTE的起始条件改成bom.UsedOnRn = '$parentPartRn'，每个父部件都能正确展开自己的BOM层级，不会再查固定根节点。
• 简化循环逻辑：删掉了冗余的内层循环和数组操作，直接遍历父部件列表逐个处理，逻辑更清晰，也不会出现变量覆盖的问题。
• 添加层级防护：在递归部分加了WHERE cte.RecursionLevel < 10，如果数据里有循环引用（比如A包含B，B又包含A），能强制终止递归，避免PowerShell卡死。
• 清晰的字段区分：输出的CSV里明确标注父/子部件的字段，不会混淆，后续处理更方便。

内容的提问来源于stack exchange，提问作者Brute