SQL Server存储过程uspGetBillOfMaterials迁移Snowflake报错求助

现有代码核心问题 #

• 参数未正确引用：Snowflake JavaScript存储过程的输入参数会自动转换为全大写变量，你当前代码硬编码了参数值1和固定日期，未使用传入的STARTPRODUCTID、CHECKDATE参数。
• SQL字符串语法错误：用单引号包裹的SQL文本内部直接使用单引号标注日期，未做转义，导致SQL解析失败，建议用JavaScript反引号（`）包裹SQL文本，无需额外转义引号。
• 多余的执行逻辑：你在执行完BOM查询后，错误尝试从结果集提取第二列作为新SQL再次执行，该逻辑完全冗余且会报错，直接删除即可。
• 表名引用不一致：锚点查询用了全路径"ADVENTUREWORKS2017"."PRODUCTION"."xxx"，递归部分只写了"PRODUCTION"."xxx"，如果存储过程执行上下文没有指定库，会触发表不存在错误。
• 返回值类型不匹配：你定义返回VARIANT类型，但实际拼接返回的是字符串，可调整返回类型为STRING，或把结果转为JSON数组返回。
• 递归配置适配：SQL Server的MAXRECURSION 25配置，Snowflake递归CTE默认最大递归深度为100，满足需求无需额外配置，如需限制可执行ALTER SESSION SET RECURSIVE_CTE_MAX_DEPTH = 25。

修正后完整代码 #

CREATE OR REPLACE PROCEDURE proc2(StartProductID FLOAT , CheckDate DATETIME)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
// 拼接SQL，引用输入参数（注意参数全大写）
const retrieveQueriesSql = `
WITH BOM_CTE ("ProductAssemblyID", "ComponentID", "ComponentDesc", "PerAssemblyQty", "StandardCost", "ListPrice", "BOMLevel", "RecursionLevel")
AS (
    -- 锚点查询
    SELECT b."ProductAssemblyID", b."ComponentID", p."Name", b."PerAssemblyQty", p."StandardCost", p."ListPrice", b."BOMLevel", 0 
    FROM "ADVENTUREWORKS2017"."PRODUCTION"."BillOfMaterials" b
    INNER JOIN "ADVENTUREWORKS2017"."PRODUCTION"."Product" p 
        ON b."ComponentID" = p."ProductID" 
    WHERE b."ProductAssemblyID" = ${STARTPRODUCTID}
        AND TO_TIMESTAMP('${CHECKDATE}') >= b."StartDate" 
        AND TO_TIMESTAMP('${CHECKDATE}') <= IFNULL(b."EndDate", TO_TIMESTAMP('${CHECKDATE}'))
    UNION ALL
    -- 递归查询
    SELECT b."ProductAssemblyID", b."ComponentID", p."Name", b."PerAssemblyQty", p."StandardCost", p."ListPrice", b."BOMLevel", "RecursionLevel" + 1 
    FROM "BOM_CTE" cte
    INNER JOIN "ADVENTUREWORKS2017"."PRODUCTION"."BillOfMaterials" b 
        ON b."ProductAssemblyID" = cte."ComponentID"
    INNER JOIN "ADVENTUREWORKS2017"."PRODUCTION"."Product" p 
        ON b."ComponentID" = p."ProductID" 
    WHERE TO_TIMESTAMP('${CHECKDATE}') >= b."StartDate" 
        AND TO_TIMESTAMP('${CHECKDATE}') <= IFNULL(b."EndDate", TO_TIMESTAMP('${CHECKDATE}'))
)
SELECT 
    b."ProductAssemblyID", 
    b."ComponentID", 
    b."ComponentDesc", 
    SUM(b."PerAssemblyQty") AS "TotalQuantity", 
    b."StandardCost", 
    b."ListPrice", 
    b."BOMLevel", 
    b."RecursionLevel"
FROM "BOM_CTE" b
GROUP BY 
    b."ComponentID", 
    b."ComponentDesc", 
    b."ProductAssemblyID", 
    b."BOMLevel", 
    b."RecursionLevel", 
    b."StandardCost", 
    b."ListPrice"
ORDER BY 
    b."BOMLevel", 
    b."ProductAssemblyID", 
    b."ComponentID"
`;

// 直接执行查询
const rs = snowflake.execute({sqlText: retrieveQueriesSql});

// 拼接返回结果
let returnValue = "";
if (rs.next()) {
    returnValue += `${rs.getColumnValue(1)}, ${rs.getColumnValue(2)}`;
}
while (rs.next()) {
    returnValue += `\n${rs.getColumnValue(1)}, ${rs.getColumnValue(2)}`;
}

return returnValue;
$$;

测试调用 #

-- 测试参数同你之前的硬编码值
CALL proc2(1, '2008-04-30 00:00:00.000');

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