How to Use the RETURN Command in Snowflake Scripting | Return as Tables

Published: 02 August 2024
on channel: Data World Solution

In this video, we dive into the RETURN command in Snowflake scripting. Learn how to effectively use RETURN to control the flow of your scripts, return values from stored procedures, and enhance your Snowflake scripts' functionality. Whether you're new to Snowflake or looking to refine your scripting skills, this tutorial will guide you through the essentials of the RETURN command with practical examples

------------------------------------------------------------------------

CREATE OR REPLACE TABLE sales (
id INT,
amount FLOAT
);

INSERT INTO sales (id, amount) VALUES
(1, 100.0),
(2, 200.0),
(3, 300.0);

select * from sales;

CREATE OR REPLACE PROCEDURE get_total_sales()
RETURNS Float
LANGUAGE SQL
AS
DECLARE
v_total_sales FLOAT;
BEGIN
-- Calculate the total sales
SELECT SUM(amount) INTO v_total_sales FROM sales;

-- Return the total sales
RETURN v_total_sales;
END;

call get_total_sales();

--------------------------------------------------

CREATE OR REPLACE PROCEDURE get_all_sales()
RETURNS TABLE (id INT, amount FLOAT)
LANGUAGE SQL
AS
DECLARE
v_rs RESULTSET;
BEGIN
-- Execute the query and store the result set
v_rs := (SELECT * FROM sales);

-- Return the result set
RETURN TABLE(v_rs);
END;

CALL get_all_sales();

-------------------------------------------------

-- without specifing table data type

CREATE OR REPLACE PROCEDURE get_all_sales()
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
v_rs RESULTSET;
BEGIN
-- Execute the query and store the result set
v_rs := (SELECT * FROM sales);

-- Return the result set
RETURN TABLE(v_rs);
END;

CALL get_all_sales();

-----------------------------

CREATE OR REPLACE PROCEDURE CUSTOMER_MK(V_C_MKTSEGMENT VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
DECLARE
v_rs RESULTSET;
BEGIN
-- Execute the query and store the result set
v_rs := (select *
from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
where C_MKTSEGMENT = :V_C_MKTSEGMENT
);

-- Return the result set
return table(v_rs);
end;

call CUSTOMER_MK('AUTOMOBILE');

SELECT DISTINCT C_MKTSEGMENT FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

---------------------------------------------------------------------------------
#Snowflake, #SnowflakeScripting, #RETURNCommand, #SQL, #DataEngineering, #DatabaseTutorial, #SnowflakeTutorial, #DataScience, #SQLScripting, #TechTutorial, #DataAnalytics, #Programming

Watch video How to Use the RETURN Command in Snowflake Scripting | Return as Tables online without registration, duration hours minute second in high quality. This video was added by user Data World Solution 02 August 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 7 once and liked it people.