Snowflake存储过程中如何将变量结果用于后续SELECT语句

你代码的报错根源是SQL存储过程的语法结构不符合规范：

• DECLARE块必须放在所有执行语句之前，不能在SET语句之后
• SQL存储过程中不能直接用SET给本地变量赋值，需使用SELECT ... INTO或LET语法
• 结果集的定义方式不符合Snowflake SQL存储过程的要求

一、正确的存储过程写法（单值变量场景） #

如果deptid=10返回的是单个值，可按以下方式编写存储过程：

USE SCHEMA test.public;
CREATE OR REPLACE PROCEDURE empresult()
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  empresult INT; -- 声明变量并指定对应数据类型
BEGIN
  -- 将查询结果赋值给变量
  SELECT deptid INTO empresult FROM TEST.PUBLIC.DEPT WHERE deptid=10;
  -- 定义结果集，用冒号:引用变量
  LET res RESULTSET := (SELECT * FROM emp WHERE deptno IN (:empresult));
  RETURN TABLE(res);
END;
$$;

二、同一会话内跨语句使用变量 #

若你想在存储过程外的其他SELECT语句中复用变量值，可直接使用会话变量，无需存储过程：

-- 设置会话变量
SET empresult = (SELECT deptid FROM TEST.PUBLIC.DEPT WHERE deptid=10);
-- 同一会话内的后续查询直接调用变量
SELECT * FROM emp WHERE deptno IN ($empresult);

该变量会在当前会话持续有效，直到会话结束或手动重置。

三、多值变量的处理方式 #

如果DEPT表中deptid=10可能返回多个值，需用数组变量存储：

USE SCHEMA test.public;
CREATE OR REPLACE PROCEDURE empresult()
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  empresult ARRAY;
BEGIN
  -- 将多值查询结果转为数组
  SELECT ARRAY_AGG(deptid) INTO empresult FROM TEST.PUBLIC.DEPT WHERE deptid=10;
  -- 使用ARRAY_CONTAINS筛选数据
  LET res RESULTSET := (SELECT * FROM emp WHERE ARRAY_CONTAINS(deptno::VARIANT, :empresult));
  RETURN TABLE(res);
END;
$$;

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