How to create and call a stored procedure in SQL (with input parameters)

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 of a specific year. You can achieve these results with the following snippet of Snowflake Script (SQL) code, which creates a procedure passing a numeric parameter called YEAR_SEL:

CREATE OR REPLACE PROCEDURE ExtractCriticalOrdersYear(YEAR_SEL NUMBER)
    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'
        AND ExtractYear(order_id)=:YEAR_SEL;
        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 and the year value inside the parentheses.

CALL ExtractCriticalOrdersYear(2013);

SELECT *
FROM CRITICAL_ORDERS
LIMIT 10;

Query the information schema