Snowflake SQL存储过程LIMIT/OFFSET参数传递报错排查

错误原因 #

Snowflake的SQL存储过程中，静态SQL的LIMIT/OFFSET子句不支持直接引用存储过程参数（如:PAGE_SIZE）。这类语法位置需要常量值或运行时可直接求值的表达式，而直接使用绑定变量会触发解析逻辑冲突，导致报错信息中出现的::符号（Snowflake处理绑定变量的内部标识），最终引发语法错误。

正确实现方式 #

要实现动态分页，需通过**动态SQL（EXECUTE IMMEDIATE）**拼接包含分页参数的查询语句，避免在静态SQL中直接引用存储过程参数。以下提供两种可行方案：

方案1：字符串拼接动态SQL（简单直接） #

修改后的存储过程代码：

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.GET_FILTERED_PAGINATION_PROVIDERS(
    "MONTH_START" VARCHAR(20), 
    "MONTH_END" VARCHAR(20), 
    "IP_FILTER" VARCHAR(100), 
    "PROPOSALS_TABLE_NAME" VARCHAR(100),
    "TIER" VARCHAR(20),
    "CITY" VARCHAR(20),
    "TYPE" VARCHAR(20),
    "PAGE_OFFSET" INTEGER,
    "PAGE_SIZE" INTEGER
)
RETURNS TABLE (
    "PROVIDERNAMEADJUSTED" VARCHAR(250), 
    "PROVIDERTYPEADJUSTED" VARCHAR(250), 
    "PROVIDERCITYADJUSTED" VARCHAR(250), 
    "STANDARDTIER" VARCHAR(250), 
    "TOTALACTUALCOST" FLOAT, 
    "TOTALCHANGE" FLOAT, 
    "TOTALCHANGEPERCENT" FLOAT
)
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE
    sql_stmt STRING;
    res RESULTSET;
BEGIN
    -- 拼接完整查询语句，将所有参数嵌入对应位置
    sql_stmt := '
        SELECT 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER, 
            SUM(PROPOSAL_CLAIMS.ACTUALCOST) AS TOTALACTUALCOST, 
            SUM(PROPOSAL_CLAIMS.CHANGE) AS TOTALCHANGE, 
            SUM(PROPOSAL_CLAIMS.CHANGE) / SUM(PROPOSAL_CLAIMS.ACTUALCOST) * 100 AS TOTALCHANGEPERCENT
        FROM 
            (
                SELECT 
                    c.PROGRAMNAME, 
                    c.PROGRAMTPA, 
                    c.PROVIDERCODE, 
                    c.SERVICECODE, 
                    c.NOOFUNITS, 
                    c.ACTUALCOST, 
                    c.INCURRALMONTHBRACKET, 
                    c.CLAIMTYPEIBNR, 
                    p.CLEANEDPROPOSAL, 
                    p.CLEANEDPROPOSAL * c.NOOFUNITS AS PROPOSEDCOST, 
                    PROPOSEDCOST - c.ACTUALCOST AS CHANGE 
                FROM 
                    CLAIMSDATATCT c 
                INNER JOIN 
                    TABLE(' || :PROPOSALS_TABLE_NAME || ') p ON c.SERVICECODE = p.SERVICECODE 
                WHERE 
                    c.INCURRALMONTHBRACKET >= TO_NUMBER(''' || :MONTH_START || ''') 
                    AND c.INCURRALMONTHBRACKET <= TO_NUMBER(''' || :MONTH_END || ''') 
                    AND c.CLAIMTYPEIBNR NOT LIKE 
                        CASE WHEN LOWER(''' || :IP_FILTER || ''') = ''exclude ip'' THEN ''Inpatient'' END
            ) AS PROPOSAL_CLAIMS
        LEFT JOIN 
            (
                SELECT 
                    programname, 
                    programtpa, 
                    providernameadjusted, 
                    providercode,
                    CASE WHEN lower(providercityadjustedgroup) = ''northern emirates'' THEN ''NE'' ELSE providercityadjustedgroup END AS providercityadjustedgroup, 
                    standardtier, 
                    CASE WHEN providertypeadjusted ILIKE ''hospital'' THEN ''Hospital'' ELSE ''Clinic'' END AS providertypeadjusted
                FROM 
                    tct_webapp.dbo.Map_Provider 
                WHERE 
                    lower(standardtier) = lower(''' || :TIER || ''')
                    AND lower(providercityadjustedgroup) = lower(''' || :CITY || ''')
                    AND lower(providertypeadjusted) = lower(''' || :TYPE || ''')
            ) AS PROVIDER 
        ON 
            PROPOSAL_CLAIMS.PROGRAMNAME = PROVIDER.PROGRAMNAME 
            AND PROPOSAL_CLAIMS.PROGRAMTPA = PROVIDER.PROGRAMTPA 
            AND PROPOSAL_CLAIMS.PROVIDERCODE = PROVIDER.PROVIDERCODE 
        GROUP BY 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER
         LIMIT ' || :PAGE_SIZE || ' OFFSET ' || :PAGE_OFFSET || ';
    ';
    
    -- 执行动态SQL并返回结果集
    res := (EXECUTE IMMEDIATE :sql_stmt);
    RETURN TABLE(res);
END;
$$;

方案2：绑定变量+动态SQL（防SQL注入，生产环境推荐） #

如果参数来自不可信来源，直接字符串拼接存在注入风险，可使用绑定变量传递分页参数：

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.GET_FILTERED_PAGINATION_PROVIDERS(
    "MONTH_START" VARCHAR(20), 
    "MONTH_END" VARCHAR(20), 
    "IP_FILTER" VARCHAR(100), 
    "PROPOSALS_TABLE_NAME" VARCHAR(100),
    "TIER" VARCHAR(20),
    "CITY" VARCHAR(20),
    "TYPE" VARCHAR(20),
    "PAGE_OFFSET" INTEGER,
    "PAGE_SIZE" INTEGER
)
RETURNS TABLE (
    "PROVIDERNAMEADJUSTED" VARCHAR(250), 
    "PROVIDERTYPEADJUSTED" VARCHAR(250), 
    "PROVIDERCITYADJUSTED" VARCHAR(250), 
    "STANDARDTIER" VARCHAR(250), 
    "TOTALACTUALCOST" FLOAT, 
    "TOTALCHANGE" FLOAT, 
    "TOTALCHANGEPERCENT" FLOAT
)
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE
    sql_stmt STRING;
    res RESULTSET;
BEGIN
    -- 定义带占位符的SQL模板
    sql_stmt := '
        SELECT 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER, 
            SUM(PROPOSAL_CLAIMS.ACTUALCOST) AS TOTALACTUALCOST, 
            SUM(PROPOSAL_CLAIMS.CHANGE) AS TOTALCHANGE, 
            SUM(PROPOSAL_CLAIMS.CHANGE) / SUM(PROPOSAL_CLAIMS.ACTUALCOST) * 100 AS TOTALCHANGEPERCENT
        FROM 
            (
                SELECT 
                    c.PROGRAMNAME, 
                    c.PROGRAMTPA, 
                    c.PROVIDERCODE, 
                    c.SERVICECODE, 
                    c.NOOFUNITS, 
                    c.ACTUALCOST, 
                    c.INCURRALMONTHBRACKET, 
                    c.CLAIMTYPEIBNR, 
                    p.CLEANEDPROPOSAL, 
                    p.CLEANEDPROPOSAL * c.NOOFUNITS AS PROPOSEDCOST, 
                    PROPOSEDCOST - c.ACTUALCOST AS CHANGE 
                FROM 
                    CLAIMSDATATCT c 
                INNER JOIN 
                    TABLE(?) p ON c.SERVICECODE = p.SERVICECODE 
                WHERE 
                    c.INCURRALMONTHBRACKET >= TO_NUMBER(?) 
                    AND c.INCURRALMONTHBRACKET <= TO_NUMBER(?) 
                    AND c.CLAIMTYPEIBNR NOT LIKE 
                        CASE WHEN LOWER(?) = ''exclude ip'' THEN ''Inpatient'' END
            ) AS PROPOSAL_CLAIMS
        LEFT JOIN 
            (
                SELECT 
                    programname, 
                    programtpa, 
                    providernameadjusted, 
                    providercode,
                    CASE WHEN lower(providercityadjustedgroup) = ''northern emirates'' THEN ''NE'' ELSE providercityadjustedgroup END AS providercityadjustedgroup, 
                    standardtier, 
                    CASE WHEN providertypeadjusted ILIKE ''hospital'' THEN ''Hospital'' ELSE ''Clinic'' END AS providertypeadjusted
                FROM 
                    tct_webapp.dbo.Map_Provider 
                WHERE 
                    lower(standardtier) = lower(?)
                    AND lower(providercityadjustedgroup) = lower(?)
                    AND lower(providertypeadjusted) = lower(?)
            ) AS PROVIDER 
        ON 
            PROPOSAL_CLAIMS.PROGRAMNAME = PROVIDER.PROGRAMNAME 
            AND PROPOSAL_CLAIMS.PROGRAMTPA = PROVIDER.PROGRAMTPA 
            AND PROPOSAL_CLAIMS.PROVIDERCODE = PROVIDER.PROVIDERCODE 
        GROUP BY 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER
         LIMIT ? OFFSET ?;
    ';
    
    -- 通过USING子句传递绑定变量，按占位符顺序对应
    res := (EXECUTE IMMEDIATE :sql_stmt 
            USING :PROPOSALS_TABLE_NAME, :MONTH_START, :MONTH_END, :IP_FILTER,
                  :TIER, :CITY, :TYPE, :PAGE_SIZE, :PAGE_OFFSET);
    RETURN TABLE(res);
END;
$$;

调用验证 #

原调用语句无需修改，可直接执行：

CALL GET_FILTERED_PAGINATION_PROVIDERS('202201', '202212', 'Exclude IP', 'Proposals_igor', 'Tier 1', 'Dubai', 'Clinic', 15, 15);

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