How to assign the returned value from a Snowflake SQL stored procedure into variable?-CodePudding

I am creating a stored procedure in Snowflake that returns number of rows in a table. Here is the code for the procedure and the result.

CREATE OR REPLACE PROCEDURE CDS_EXTRACT_CHECK_ROWCOUNT(STAGE_DATABASE varchar, STAGE_SCHEMA varchar, STAGE_TABLE varchar)
RETURNS table (a int)
LANGUAGE SQL
AS
    DECLARE
    stmt string;
    res resultset;
    rowcount int;
    BEGIN
        stmt := 'SELECT COUNT(*) FROM ' || :STAGE_DATABASE || '.' || :STAGE_SCHEMA || '.' || :STAGE_TABLE || ';'; 
        res := (EXECUTE IMMEDIATE :stmt);
        RETURN TABLE(res);
    END
;

I want to execute this stored procedure within another procedure and store the returned value to a variable:

rowcount := CALL CDS_EXTRACT_CHECK_ROWCOUNT(:STAGE_DATABASE, :STAGE_SCHEMA, :STAGE_TABLE);

Thanks in advance

CodePudding user response：

You can use the below version:

CREATE OR REPLACE PROCEDURE CDS_EXTRACT_CHECK_ROWCOUNT(
    STAGE_DATABASE varchar, 
    STAGE_SCHEMA varchar, 
    STAGE_TABLE varchar
)
RETURNS INT
LANGUAGE SQL
AS
DECLARE
    table_path string;
    rowcount int;
BEGIN
    table_path := :STAGE_DATABASE || '.' || :STAGE_SCHEMA || '.' || :STAGE_TABLE;
    SELECT COUNT(*) INTO :rowcount  FROM identifier(:table_path); 

    return :rowcount;
END
;

CodePudding user response：

You can use RESULT_SCAN to read the value returning from your stored procedure:

https://docs.snowflake.com/en/sql-reference/functions/result_scan.html

For example:

...
CALL CDS_EXTRACT_CHECK_ROWCOUNT(:STAGE_DATABASE, :STAGE_SCHEMA, :STAGE_TABLE);
select $1 into :rowcount from table(result_scan(last_query_id()));
...