sql - How to reference a variable set outside a procedure in Snowflake? - Stack Overflow

admin管理员组

文章数量:1124776

I have this code of a procedure where I call a document AI build:

CREATE OR REPLACE PROCEDURE myproc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS

BEGIN

        CREATE OR REPLACE TEMPORARY TABLE my_table AS
        SELECT
            FILE_NAME,
            DOCAI_BUILD_NAME!PREDICT(
                GET_PRESIGNED_URL(@DOCS, FILE_NAME), 3
            ) AS pred
        FROM mytable;


    RETURN 'OK';
END;

Then this procedure is called by a task:

CREATE OR REPLACE TASK mytask
WAREHOUSE = mywarehouse
SCHEDULE = '1 minute'
USER_TASK_TIMEOUT_MS = 86400000
WHEN SYSTEM$STREAM_HAS_DATA('mystream')
AS
    CALL myproc();

I want to set the version of Document AI build in a variable:

SET DOC_AI_VERSION = 3;

I want to use the variable set to the version of the DocumentAI build (which is here set to 3) directly from the variable that I set.

Also, is there a way to set the DocumentAI build name in a variable (which is set here to DOCAI_BUILD_NAME) and also reference the variable inside the procedure.

I have tried to call the variable DOC_AI_VERSION via GET_PRESIGNED_URL(@DOCS, FILE_NAME), $DOC_AI_VERSION but this doesn't work. It logs an error:

$DOC_AI_VERSION' does not exist

I have this code of a procedure where I call a document AI build:

CREATE OR REPLACE PROCEDURE myproc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS

BEGIN

        CREATE OR REPLACE TEMPORARY TABLE my_table AS
        SELECT
            FILE_NAME,
            DOCAI_BUILD_NAME!PREDICT(
                GET_PRESIGNED_URL(@DOCS, FILE_NAME), 3
            ) AS pred
        FROM mytable;


    RETURN 'OK';
END;

Then this procedure is called by a task:

CREATE OR REPLACE TASK mytask
WAREHOUSE = mywarehouse
SCHEDULE = '1 minute'
USER_TASK_TIMEOUT_MS = 86400000
WHEN SYSTEM$STREAM_HAS_DATA('mystream')
AS
    CALL myproc();

I want to set the version of Document AI build in a variable:

SET DOC_AI_VERSION = 3;

I want to use the variable set to the version of the DocumentAI build (which is here set to 3) directly from the variable that I set.

Also, is there a way to set the DocumentAI build name in a variable (which is set here to DOCAI_BUILD_NAME) and also reference the variable inside the procedure.

I have tried to call the variable DOC_AI_VERSION via GET_PRESIGNED_URL(@DOCS, FILE_NAME), $DOC_AI_VERSION but this doesn't work. It logs an error:

$DOC_AI_VERSION' does not exist

• sql
• variables
• snowflake-cloud-data-platform

Share Improve this question Follow edited 2 days ago marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked 2 days ago Mejdi DallelMejdi Dallel 1366 bronze badges 1

• You don't call a variable - you can only call code. You would use or reference that variable (or more precisely: its value) – marc_s Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

you can retrieve the value like (SELECT $DOC_AI_VERSION).

Setting the variable

SET DOC_AI_VERSION = 3;

Declare the variables in your procedure

CREATE OR REPLACE PROCEDURE myproc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    DOC_AI_VERSION STRING;
    SQL_STATEMENT STRING;
BEGIN
    DOC_AI_VERSION := (SELECT $DOC_AI_VERSION);
    SQL_STATEMENT := 'CREATE OR REPLACE TEMPORARY TABLE my_table AS
                      SELECT
                          FILE_NAME,
                          ' ||  DOC_AI_VERSION || ' as variable_value
                            
                      FROM mytable';

    RETURN SQL_STATEMENT;

CALL myproc() ;

sql_statement prints as:

CREATE OR REPLACE TEMPORARY TABLE my_table AS
          SELECT
          FILE_NAME,
          3 as variable_value
          FROM mytable

本文标签： sqlHow to reference a variable set outside a procedure in SnowflakeStack Overflow