Snowflake SQL中已声明变量无法使用的问题排查

问题背景 #

原可正常执行的地理查询脚本：

return (
  select * from GDD_DATA 
  where ST_INTERSECTS((
    select top 1 GEOMETRY from GDD_DATA 
    where DATA_SOURCE_ID='SKSNATURVARDEN'),
  GEOMETRY)
);

为实现参数化改写为存储过程后报错：

declare geo GEOGRAPHY := (
  select top 1 GEOMETRY from GDD_DATA 
  where DATA_SOURCE_ID='SKSNATURVARDEN');
begin
  -- return geo; 单独返回变量可正常运行
  return (select * from GDD_DATA where ST_INTERSECTS(geo, GEOMETRY));
end;

报错信息：

invalid identifier 'GEO' (line 2)

尝试${geo}、$geo、:geo等语法均无效，后续调整赋值后ST_ASWKB(geo)可正常执行，但ST_INTERSECTION(geo, GEOMETRY)仍报错，仅直接写入子查询可正常运行。

正确写法与解决要点 #

1. 变量引用的正确语法 #

在Snowflake存储过程的SQL查询语句中，引用已声明的变量必须使用**:变量名**的格式，调整后的脚本：

declare geo GEOGRAPHY := (
  select top 1 GEOMETRY from GDD_DATA 
  where DATA_SOURCE_ID='SKSNATURVARDEN');
begin
  return (select * from GDD_DATA where ST_INTERSECTS(:geo, GEOMETRY));
end;

2. 类型匹配检查 #

报错的核心原因可能是变量类型与表中列类型不匹配：

• 若GDD_DATA表的GEOMETRY列类型为GEOMETRY，需将变量声明改为GEOMETRY：

  declare geo GEOMETRY := (select top 1 GEOMETRY from GDD_DATA where DATA_SOURCE_ID='SKSNATURVARDEN');
  begin
    return (select * from GDD_DATA where ST_INTERSECTS(:geo, GEOMETRY));
  end;
  

• 若存在维度或类型差异，可通过显式转换统一类型：

  declare geo GEOGRAPHY := (select top 1 ST_GEOGFROMWKB(GEOMETRY) from GDD_DATA where DATA_SOURCE_ID='SKSNATURVARDEN');
  begin
    return (select * from GDD_DATA where ST_INTERSECTS(:geo, ST_GEOGFROMWKB(GEOMETRY)));
  end;
  

3. 替代变量声明方式（可选） #

若上述方法仍有问题，可尝试用LET替代DECLARE声明变量，部分场景下兼容性更好：

let geo GEOGRAPHY := (select top 1 GEOMETRY from GDD_DATA where DATA_SOURCE_ID='SKSNATURVARDEN');
begin
  return (select * from GDD_DATA where ST_INTERSECTS(:geo, GEOMETRY));
end;

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