Convert SQL variables in snowflake

user_3204086 0
Viewed 138

I am converting a SQL stored procedure to snowflake , code of which is as follows:

Create PROCEDURE <sp_name>  (@parameter)
AS
BEGIN
        SET NOCOUNT ON

Declare 
@A varchar(max), 
@B smallint = 0,
@C int = 0,
        DECLARE @D smallint,
            @E smalldatetime,
            @F smallint

Need some help to understand how will I convert the above variables in snowflake to be consumed by sql statement.

snowflake-cloud-data-platform sql javascript
1 Answers

I would look into Snowflake Scripting, you can use variables in expressions and SQL statements. This should have the equivalent functionality you are looking for.

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/ https://docs.snowflake.com/en/sql-reference-snowflake-scripting.html https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html#examples-of-using-variables

create or replace procedure myprocedure()
returns table(A varchar, B smallint, C integer, D smallint, E 
timestamp_ntz, F smallint)
language SQL
as
$$
declare
A varchar default null; 
B smallint default 0;
C integer default 0;
D smallint default -1;
E timestamp_ntz default current_timestamp();
F smallint default 0;
res RESULTSET default (select :A,:B,:C,:D,:E,:F);
begin
  return table(res);
end
$$
;    

call myprocedure();
A   B   C   D   E   F
0   0   -1  2022-02-23 11:53:21.230 0
user_141589640
Related