How to Declare Variables in Snowflake Scripting: Declaration, Scope, Assignment, SELECT Statements

In this video, you'll learn how to declare variables in Snowflake scripting, understand their scope, assign values, and use them effectively in SELECT SQL statements. Whether you're new to Snowflake or looking to deepen your scripting skills, this tutorial will guide you through the essentials of working with variables in Snowflake.

code -

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

declare
v_date date;
v_no number:=10;
v_name varchar2(100) :='DataWorld';
begin
return v_no;
end;


-- Declaring a variable
DECLARE
profit number(38, 2) DEFAULT 0.0;
BEGIN
LET cost number(38, 2) := 100.0;
LET revenue number(38, 2) DEFAULT 110.0;

profit := revenue - cost;
RETURN profit;
END;

-- SNOWSQL

EXECUTE IMMEDIATE
$$
DECLARE
profit number(38, 2) DEFAULT 0.0;
BEGIN
LET cost number(38, 2) := 100.0;
LET revenue number(38, 2) DEFAULT 110.0;

profit := revenue - cost;
RETURN profit;
END;
$$
;






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

declare
v_date date:= current_date();
v_no number:=10*2;
v_pi number(10,2):= 3.14;
begin
v_date:=v_date+10;
-- return v_date;
return v_pi;

end;


-- Examples of using variables


DECLARE
w INTEGER;
x INTEGER DEFAULT 0;
dt DATE;
result_string VARCHAR;
BEGIN
w := 1; -- Assign a value.
w := 24 * 7; -- Assign the result of an expression.
dt := '2020-09-30'::DATE; -- Explicit cast.
dt := '2020-09-30'; -- Implicit cast.
result_string := w::VARCHAR || ', ' || dt::VARCHAR;
RETURN result_string;
END;


-- how to use the LET statement to create the cost and revenue variables outside of the DECLARE section of the block

DECLARE
profit number(38, 2) DEFAULT 0.0;
BEGIN
LET cost number(38, 2) := 200.0;
LET revenue number(38, 2) DEFAULT 300.0;

profit := revenue - cost;
RETURN profit;
END;


----------------------------
-- Setting variables to the results of a SELECT statement

-- Create the sales table
CREATE OR REPLACE TABLE sales (
sale_id INT,
sale_date DATE,
category STRING,
amount DECIMAL(10, 2)
);

-- Insert sample data into the sales table
INSERT INTO sales (sale_id, sale_date, category, amount) VALUES
(1, '2024-01-01', 'Electronics', 100.00),
(2, '2024-01-02', 'Electronics', 150.00),
(3, '2024-01-03', 'Electronics', 200.00);





select *
from
Sales;

--write a pl/sql block to print the sale_date and the category for Sale id =1

-- In a Snowflake Scripting block, you can use the INTO clause to set variables to the values of expressions specified in a SELECT clause:

declare
v_sale_date date;
v_category varchar(50);
begin
-- The select should retrieve 1 record, otherwise it will be exception
select sale_date,category into v_sale_date, v_category from sales where sale_id = '1';
return v_sale_date || ' , ' || v_category;
end;




-- scope of a variable


declare
v_global varchar2(100):='this is a global variable';
result_string varchar;
begin

declare
v_local varchar2(100):='this is a local variable';
begin
result_string := v_global ||' , ' ||v_local;
end;

return result_string;

end;
----------------------------------------------------------

declare
v_Global_name varchar2(100):='Data World Global';
v_birthday date:='20-Jul-2023';
v_result_string varchar;
begin
declare
v_Local_name varchar2(100):='Data World Local';
v_birthday date:='5-Apr-2024';
begin
v_result_string := v_Global_name || v_birthday ||v_Local_name||v_birthday;
-- return v_result_string;
end;
return v_result_string;
end;
-----------------------------------------------------------

#Snowflake #SnowflakeScripting #SQL #SQLTutorial #DataEngineering #DataAnalytics #DataScience #Variables #Programming #Database #TechTutorial #Coding #BigData #DataManagement #LearnSQL