My First Snowflake Procedure

Having not created stored procedures in Snowflake before I wanted to give it a go. My preference is for the more newly released SQL style procedures over the Javascript ones, though I fully intend to give them a stab as well. Below is the code I used to create said my first Snowflake Procedure. This procedure just takes in a table name and returns the count of rows in said table. Not overly useful but for me it has some useful proof of concepts, not only how to create a basic procedure in Snowflake but also how to run dynamic SQL Have a look through the code and feel free to ask any questions

	-- stanadard create or replace syntax with language as SQL
	create
	or replace procedure tab_count(table_name varchar) returns number language sql as
	-- declaration of additional variable
	DECLARE v_count INT;
	v_sql varchar2(1000);
	BEGIN
	-- pass table name into dynamic SQL
	v_sql: = concat('SELECT COUNT(*) FROM ', table_name);
	-- run the query generated by the dynamic SQL
	execute immediate v_sql;
	-- retrieve the output from the dynamic SQL
	select
	$1 into v_count
	from
	table(result_scan(last_query_id()));
	-- returned the stored value of the variable
	return v_count;
	end;

view raw FirstSnowflakeProc.sql hosted with ❤ by GitHub

Here is proof it works with me running the procedure and it producing the correct result.