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.
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