如何从Snowflake存储过程获取COUNT结果？解决赋值报错问题

存储过程代码 #

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.COUNT_FILTERED_PROVIDERS(
    "TIER" VARCHAR(20), 
    "CITY" VARCHAR(20), 
    "TYPE" VARCHAR(20),
    "IS_DESCENDING" BOOLEAN DEFAULT FALSE
)
RETURNS FLOAT
LANGUAGE SQL
EXECUTE AS OWNER
AS
DECLARE
    stmt VARCHAR;
    res FLOAT;
BEGIN
    stmt := 'SELECT COUNT(*) AS TOTAL_ROWS FROM ' || :PROVIDERS_TABLE_NAME || '
      WHERE 1 = 1';

    IF (LOWER(:CITY) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERCITYADJUSTED = ''' || :CITY || '''';
    END IF;

    IF (LOWER(:TIER) <> 'all') THEN
        stmt := stmt || ' AND STANDARDTIER = ''' || :TIER || '''';
    END IF;

    IF (LOWER(:TYPE) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERTYPEADJUSTED = ''' || :TYPE || '''';
    END IF;

    res := (EXECUTE IMMEDIATE stmt);
    RETURN res;
END;

调用语句 #

CALL COUNT_FILTERED_PROVIDERS('all', 'all', 'Clinic', 'Providers_123');

报错信息 #

Invalid expression value (?SqlExecuteImmediateDynamic?) for assignment.

解决方案 #

问题分析 #

1. 动态SQL赋值方式错误：不能直接将EXECUTE IMMEDIATE的结果赋值给变量，需通过SELECT ... INTO语法捕获查询结果。
2. 参数不匹配：存储过程未定义PROVIDERS_TABLE_NAME参数，但调用时传入了表名作为第四个参数，与原参数列表（TIER、CITY、TYPE、IS_DESCENDING）不符，导致动态SQL引用未定义变量。
3. SQL注入风险：直接拼接字符串存在注入风险，建议使用绑定变量。

修改后的存储过程 #

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.COUNT_FILTERED_PROVIDERS(
    "TIER" VARCHAR(20), 
    "CITY" VARCHAR(20), 
    "TYPE" VARCHAR(20),
    "PROVIDERS_TABLE_NAME" VARCHAR(100), -- 新增表名参数
    "IS_DESCENDING" BOOLEAN DEFAULT FALSE
)
RETURNS FLOAT
LANGUAGE SQL
EXECUTE AS OWNER
AS
DECLARE
    stmt VARCHAR;
    res FLOAT;
BEGIN
    stmt := 'SELECT COUNT(*) FROM IDENTIFIER(:TABLE_NAME)
      WHERE 1 = 1';

    IF (LOWER(:CITY) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERCITYADJUSTED = :CITY';
    END IF;

    IF (LOWER(:TIER) <> 'all') THEN
        stmt := stmt || ' AND STANDARDTIER = :TIER';
    END IF;

    IF (LOWER(:TYPE) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERTYPEADJUSTED = :TYPE';
    END IF;

    -- 使用SELECT ... INTO捕获动态SQL结果
    EXECUTE IMMEDIATE stmt 
    INTO res
    USING :PROVIDERS_TABLE_NAME, :CITY, :TIER, :TYPE;

    RETURN res;
END;

正确调用语句 #

CALL COUNT_FILTERED_PROVIDERS('all', 'all', 'Clinic', 'Providers_123');

关键修改点说明 #

• 新增PROVIDERS_TABLE_NAME参数，匹配调用时传入的表名。
• 使用IDENTIFIER(:TABLE_NAME)安全引用动态表名，避免SQL注入。
• 改用绑定变量（:CITY、:TIER等）替代字符串拼接，提升安全性。
• 通过EXECUTE IMMEDIATE ... INTO res语法将查询结果赋值给变量res。

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