Can't use a declared variable in Snowflake's SQL

Question

I'm successfully executing the following script:

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

Then, I wanted to parameterize it and, checking the docs, I introduced a variable like this.

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;

It produces the error:

invalid identifier 'GEO' (line 2)

I suspect that a hint on where the root cause resides is in the extended error message:

Error:'EXPRESSION_ERROR' on line 5 at position 13 : SQL compilation error: error line 2 at position 26 (line 2)

However, returning the object only (the commented-out line) works, indicating that the definition of it is correct. Still, applying that object in the call to the function says otherwise.

I've tested a lot of syntax like use ${geo}, $geo, let geo..., :geo, set geo := ..., without success. What's the proper syntax?

Based on the comments and answers, I eliminated the root cause to be the declaration. Running the following works for ST_ASWKB but not for ST_INTERSECTION.

declare geo GEOGRAPHY;
--create temporary table temp (Geometry GEOGRAPHY);
begin
  select top 1 GEOMETRY into geo from GDD_DATA
  where DATA_SOURCE_ID='SKSNATURVARDEN';
  return (select * from GDD_DATA where ST_INTERSECTION(geo, GEOMETRY));
--return ST_ASWKB(geo);
end;

Is it possible that the issue is with the dimension of the passed geo somehow? It does work if the SELECT is pasted in explicitly as the first parameter but not when it's stored in a variable.

Error:'EXPRESSION_ERROR' on line 6 at position 7:
SQL compilation error: error line 1 at position 53 (line 1)
invalid identifier 'GEO' (line 1)

Answer 1

:= is an assign operator and can be used inside DECLARE block:

DECLARE
  geo GEOMETRY := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
    ...
END;

To use a variable inside SQL statement, it must be prefixed with colon - :geo:

DECLARE
  geo GEOMETRY  := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
   RETURN (SELECT TOP 1 geometry_col 
           FROM GDD_DATA 
           WHERE ST_INTERSECTS(:geo, geometry_col));
END;

More at: Using a Variable in a SQL Statement (Binding)

---

Important: RETURN expects a single value (one row, one column). If resultset is needed then:

DECLARE
  geo GEOMETRY  := TO_GEOMETRY('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
BEGIN
  -- using LET syntax, but can also be inside declare block
  LET res RESULTSET :=  (SELECT  id, geometry_col 
                         FROM GDD_DATA 
                         WHERE ST_INTERSECTS(:geo, geometry_col));

   RETURN TABLE(res);
END;

Output: