How to create and call a stored procedure in Snowflake

Let’s say you have a table GLOBALSUPERSTORE_ORDERS and you want to make a stored procedure that, once executed, creates (if it doesn’t already exist) a new table with only critical orders.

You can achieve these results with the following snippet of Snowflake Script (SQL) code:

CREATE OR REPLACE PROCEDURE ExtractCriticalOrders()
    RETURNS VARCHAR
    LANGUAGE SQL
AS
 $$
    DECLARE
        message VARCHAR;
    BEGIN
        CREATE OR REPLACE TABLE CRITICAL_ORDERS
        (
            ORDER_ID VARCHAR(255),
            ORDER_DATE DATE
        );
        INSERT INTO CRITICAL_ORDERS(ORDER_ID,ORDER_DATE)
        SELECT ORDER_ID,ORDER_DATE
        FROM GLOBALSUPERSTORE_ORDERS
        WHERE ORDER_PRIORITY='Critical';
        message := (SELECT CONCAT(TO_VARCHAR(COUNT(*)), ' orders are critical!') FROM CRITICAL_ORDERS);
        RETURN message;
    END;
 $$
;

Therefore you can call (execute) the procedure invoking the CALL instruction and the name of the procedure.

CALL ExtractCriticalOrders();

This will create and populate the table

SELECT *
FROM CRITICAL_ORDERS
LIMIT 10;