Assigning a Python stored procedure return value in Snowflake Scripting: CALL INTO and direct assignment, not EXECUTE IMMEDIATE

Learn how to assign a Python stored procedure return value in Snowflake Scripting: use direct assignment or CALL INTO, avoid EXECUTE IMMEDIATE errors.

Assigning the result of a Python stored procedure to a Snowflake Scripting variable can look deceptively simple, yet one misplaced construct leads to confusing parser errors. If you tried to route a procedure’s return value through EXECUTE IMMEDIATE, you likely ran into messages about unexpected INTO or RETURN. The root cause is straightforward: EXECUTE IMMEDIATE is for dynamic SQL and isn’t needed here at all.

Problem setup

Consider a Python stored procedure that returns a success string, followed by a block that tries to capture that value into a variable using EXECUTE IMMEDIATE and then return it.

CREATE OR REPLACE TEMPORARY PROCEDURE py_task_proc()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
HANDLER = 'entry'
AS
$$
def entry():
    return "Procedure executed successfully"
$$
;

DECLARE out_msg STRING;

BEGIN
  EXECUTE IMMEDIATE 'CALL py_task_proc()' INTO :out_msg;
  RETURN out_msg;
END;

SHOW VARIABLES;

This produces errors such as “Syntax error: unexpected 'INTO'” and “syntax error ... unexpected 'RETURN'”.

What actually goes wrong

EXECUTE IMMEDIATE is intended for dynamic SQL. In this scenario the statement is static and doesn’t require dynamic execution. Using EXECUTE IMMEDIATE forces an unnecessary code path and the parser rejects the INTO placement in this context. There’s a simpler and correct way to capture a procedure’s return value. It’s also worth noting that SHOW VARIABLES targets session variables, which are not the same as Snowflake Scripting variables declared inside a block.

Working approach

The call result can be assigned directly to a scripting variable, or you can ask Snowflake to put it into a variable via the CALL ... INTO syntax. Both approaches avoid dynamic SQL entirely.

CREATE OR REPLACE TEMPORARY PROCEDURE py_task_proc()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.11
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'entry'
AS
$$
def entry():
    return "Procedure executed successfully :)"
$$
;

Main block:

DECLARE 
  out_msg STRING;
BEGIN
  out_msg := (CALL py_task_proc());

  CALL py_task_proc() INTO :out_msg;

  RETURN :out_msg;
END;

Why this matters

Choosing native Snowflake Scripting constructs over dynamic SQL keeps code concise and eliminates avoidable syntax errors. It also makes control flow and data movement explicit: the procedure is invoked, the return value is captured, and the result is returned from the block. Understanding that SHOW VARIABLES reports on session-level variables, not block-scoped scripting variables, prevents misleading checks during debugging.

Takeaways

If you need a Python stored procedure’s return value in Snowflake Scripting, call it directly and assign the result, or use CALL ... INTO. Reserve EXECUTE IMMEDIATE for cases that truly require dynamic SQL, and remember that SHOW VARIABLES won’t display variables created with DECLARE inside a scripting block.