How to write a SQL stored procedures in Snowflake

As you have probably already read in the official documentation, the language in which stored procedures are written on Snowflake is not SQL but Javascript. This can be a problem for those developers who come from other databases and are not yet proficient in Js. These examples, of course, do not circumvent the rule of writing procedures in Js, but they give the developer who has to do extremely simple tasks, the possibility to start from a standard Js skeleton and insert the query written in SQL. For less simple tasks, unfortunately, it is necessary to study Javascript.

In all our examples, we will try to insert a row in the Target_SP table:

CREATE TABLE Target_SP(
	Code varchar(50) NOT NULL,
    Points INT NULL,
	Inserted TIMESTAMP NULL
);

Stored procedure without parameters

You can use this definition:

CREATE OR REPLACE PROCEDURE SP_INSERT_FIXED_CODE()
    returns STRING NOT NULL
    language JAVASCRIPT
AS
$$
var definition = `
    INSERT INTO Target_SP (Code,Inserted)
    VALUES ('A002', CURRENT_TIMESTAMP());
`
var sql = snowflake.createStatement({sqlText: definition});
var result = sql.execute();
return 'Code inserted!';
$$;

You can call the procedure using the CALL statement:

CALL SP_INSERT_FIXED_CODE();

Stored procedure with single parameter

You can use this definition:

CREATE OR REPLACE PROCEDURE SP_INSERT_INPUT_CODE(ARGUMENT VARCHAR)
returns string not null
language javascript
as
$$
var your_argument = ARGUMENT;

var definition = `
    INSERT INTO Target_SP (Code,Inserted)
    VALUES (:1, CURRENT_TIMESTAMP());
`
var sql = snowflake.createStatement(
{
sqlText: definition,
binds: [ARGUMENT]
}
);
var result = sql.execute();
return `Inserted code ${your_argument}`;

$$;

You can call the procedure using the CALL statement:

CALL SP_INSERT_INPUT_CODE('N');

Stored procedure with multiple parameters

You can use this definition:

CREATE OR REPLACE PROCEDURE SP_INSERT_INPUT_MULTI_CODE(Code VARCHAR, Points FLOAT)
returns string not null
language javascript
as
$$
var Code_args = CODE;

var definition = `
     INSERT INTO Target_SP (Code,Points,Inserted)
    VALUES (:1, :2, CURRENT_TIMESTAMP());
`
var sql = snowflake.createStatement(
{
sqlText: definition,
binds: [CODE,POINTS]
}
);
var result = sql.execute();
return `Inserted code ${Code_args}`;

$$;

You can call the procedure using the CALL statement:

CALL SP_INSERT_INPUT_MULTI_CODE('A005', 10);