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

Опубликовано: 02 Август 2024
на канале: 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

Смотрите видео How to Use the RETURN Command in Snowflake Scripting | Return as Tables онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Data World Solution 02 Август 2024, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 7 раз и оно понравилось людям.