What is the best way to set multiple variables in snowflake from a table

user_11938852 0
Viewed 693

Equivalent declaration and setting the variable as below.

DECLARE @name varchar(255)
       ,@address varchar(255)
       ,@country varchar(255)

SELECT @name = name
      ,@address = address
      ,@country = country
    FROM tbl_address
    WHERE id =5

When converting this code into snowflake SP, what's the best way to handle the declaration and setting the variable from table.

enter image description here

snowflake-sql snowflake-cloud-data-platform
1 Answers

It is possible to assgin multiple variables at once:

CREATE OR REPLACE TABLE tbl_address AS
SELECT 5 AS id, 'a' AS name, 'b' AS address, 'c' AS country;

SET (name, address, country) = (SELECT name, address, country 
                                FROM tbl_address WHERE id = 5);

SELECT $name, $address, $country;
-- a, b, c

The requirement is that the suqbquery used to fetch values cannot return more than 1 row.

user_50708790
Related