Inside a procedure, the returned object can be easily deconstructed by using the following statements right after performing the procedure call:
LET call_results VARIANT := (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));
var1 := GET(:call_results, 'Message');
var2 := GET(:call_results, 'Message2');
\
Multiple out parameter with dynamic result sets
Teradata
IN -> Teradata_05.sql
REPLACE PROCEDURE Procedure1(out product_name VARCHAR(50), out price integer)DYNAMIC RESULT SETS 2BEGIN DECLARE result_set CURSOR WITH RETURN ONLY FOR SELECT * FROM inventory; DECLARE result_set2 CURSOR WITH RETURN ONLY FOR SELECT * FROM inventory; SET price =100; SET product_name ='another2'; OPEN result_set2; OPEN result_set;END;REPLACE PROCEDURE Procedure2()BEGIN DECLARE price INTEGER; DECLARE productName varchar(10); CALL Procedure1(productName, price); INSERT INTO inventory VALUES(:productName, :price);END;CALL Procedure2();
Snowflake Scripting
OUT -> Teradata_05.sql
CREATE OR REPLACE PROCEDURE Procedure1 (out !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET' NODE ***/!!! PRODUCT_NAME VARCHAR(50), out !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OUTPUT PARAMETERS ALONG WITH DYNAMIC RESULT SET' NODE ***/!!! PRICE integer)RETURNS VARIANTLANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/23/2024" }}'EXECUTE AS CALLERAS$$ DECLARE tbl_result_set VARCHAR; tbl_result_set2 VARCHAR; return_arr ARRAY := array_construct(); BEGIN tbl_result_set :='RESULTSET_'|| REPLACE(UPPER(UUID_STRING()), '-', '_'); CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set) AS SELECT* FROM inventory; LET result_set CURSOR FOR SELECT* FROM IDENTIFIER(?); tbl_result_set2 :='RESULTSET_'|| REPLACE(UPPER(UUID_STRING()), '-', '_'); CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:tbl_result_set2) AS SELECT* FROM inventory; LET result_set2 CURSOR FOR SELECT* FROM IDENTIFIER(?); price :=100; product_name :='another2'; OPEN result_set2 USING (tbl_result_set2); return_arr := array_append(return_arr, :tbl_result_set2); OPEN result_set USING (tbl_result_set); return_arr := array_append(return_arr, :tbl_result_set);--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES ** RETURN OBJECT_CONSTRUCT('SC_RET_VALUE', :return_arr, 'product_name', :product_name, 'price', :price); END;$$;CREATE OR REPLACE PROCEDURE Procedure2 ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "07/23/2024" }}'EXECUTE AS CALLERAS$$ DECLARE price INTEGER; productName varchar(10); call_results VARIANT; BEGIN call_results := ( CALL Procedure1(:productName, :price) ); productName := :call_results:product_name; price := :call_results:price; INSERT INTO inventoryVALUES (:productName, :price); END;$$;CALL Procedure2();
Known Issues
1. Out Parameters
Snowflake Scripting does not support OUT/INOUT parameters therefore these parameters are returned at the end of the procedure's body.
2. SQL Data Access
By default, Snowflake procedures support the execution of any kind of SQL statements, including data reading or modification statements, making the SQL data access clause non-relevant. This clause will be ignored when converting the procedure.
3. Top Level Objects in Assessment Report
Elements (Temporal tables or Views) inside Stored Procedures are being counted in the Assessment report as Top Level Objects. The SnowConvert team is now working on a fix for this scenario.