Snowflake - Stored Procedures [ Error free script - ready to use]

 1. Basic Stored Procedure

create or replace procedure proc_return_str()

  RETURNS VARCHAR

  LANGUAGE javascript

  EXECUTE AS CALLER

  AS '

  return "Hello from the snowflake stored procedure!";

  ';

call proc_return_str();

2. Stored Procedure with Parameters 

CREATE OR REPLACE PROCEDURE proc_withparampass(message VARCHAR)

RETURNS VARCHAR

LANGUAGE SQL

AS

$$

BEGIN

  RETURN 'Hello from Snowflake! You passed: ' || message;

END;

$$;

CALL proc_withparampass('Snowflake Stored Procedures');

3. Stored Procedures with Update SQL statement

CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage DECIMAL(5, 2), min_rating INT)

RETURNS VARCHAR

LANGUAGE SQL

AS

$$

DECLARE

  rows_updated INT;

BEGIN

  UPDATE employees

  SET tot_salary = salary * (1 + :bonus_percentage / 100)

  WHERE performance_rating >= :min_rating;

  rows_updated := SQLROWCOUNT;

  RETURN 'Bonus applied to ' || rows_updated || ' employees.';

END;

$$;

CALL apply_bonus(5, 5);

4. Stored Procedures with Return Output as Table

CREATE OR REPLACE PROCEDURE proc_return_table(min_salary DECIMAL(10, 2))

RETURNS TABLE(emp_id INT, salary DECIMAL(10, 2))

LANGUAGE SQL

AS

$$

DECLARE

  res RESULTSET;

BEGIN

  res := (SELECT emp_id, salary FROM employees WHERE salary >= :min_salary ORDER BY salary DESC);

  RETURN TABLE(res);

END;

$$;

CALL proc_return_table(70000);

5. Stored Procedures with Insert SQL statement

CREATE OR REPLACE PROCEDURE proc_calc_writein_insertinothertable(

    start_date DATE,

    end_date DATE,

    in_region STRING

)

RETURNS STRING

LANGUAGE SQL

AS

$$

DECLARE

    v_total_sales NUMBER;

    v_total_orders NUMBER;

    v_avg_order_value NUMBER;

BEGIN

    INSERT INTO SALES_SUMMARY_LOG (

        RUN_TIMESTAMP, SALES_REGION, START_DATE, END_DATE,

        TOTAL_SALES, TOTAL_ORDERS, AVERAGE_ORDER_VALUE, STATUS

    )

    SELECT CURRENT_TIMESTAMP,:in_region, :start_date, :end_date,

        SUM(AMOUNT) AS v_total_sales, COUNT(*) AS v_total_orders, AVG(AMOUNT) AS v_avg_order_value

        ,'SUCCESS'

    FROM SALES

    WHERE ORDER_DATE BETWEEN :start_date AND :end_date

      AND SALES_REGION = :in_region;

      

    RETURN 'Summary calculation completed successfully for region: ' || :in_region ;

EXCEPTION

    WHEN OTHER THEN

        INSERT INTO SALES_SUMMARY_LOG (

            RUN_TIMESTAMP, SALES_REGION, START_DATE, END_DATE,

            TOTAL_SALES, TOTAL_ORDERS, AVERAGE_ORDER_VALUE, STATUS

        )

        VALUES (

            CURRENT_TIMESTAMP, :in_region, :start_date, :end_date,

            NULL, NULL, NULL, 'FAILED'

        );

        RETURN 'An error occurred during summary calculation.';

END;

$$;

CALL proc_calc_writein_insertinothertable('2025-01-02', '2025-01-02', 'West');

6. Stored Procedures with Calculation

Eg: 1

CREATE OR REPLACE PROCEDURE proc_simple_num_calc()

RETURNS NUMBER

LANGUAGE SQL

AS

$$

DECLARE

    profit NUMBER(38, 2) DEFAULT 0.0; -- Variable declared with a default value

    cost NUMBER(38, 2);              -- Variable declared without a default value (will be NULL initially)

BEGIN

    cost := 100.0;

    LET revenue NUMBER(38, 2) DEFAULT 110.0; -- LET can also be used inside BEGIN...END

    profit := revenue - cost;

    RETURN profit;

END;

$$;

CALL proc_simple_num_calc();

Eg: 2

CREATE OR REPLACE PROCEDURE calculate_revenue()

RETURNS NUMBER

LANGUAGE SQL

AS

$$

BEGIN

    LET sales_amount NUMBER(38, 2) := 500.0; -- Declared and initialized using :=

    LET discount_rate NUMBER(38, 2) DEFAULT 0.10; -- Declared and initialized using DEFAULT

    LET net_revenue NUMBER(38, 2);

    net_revenue := sales_amount * (1 - discount_rate);

    RETURN net_revenue;

END;

$$;

CALL calculate_revenue();

7. Stored Procedure with Cursor & Get count of a table:

CREATE OR REPLACE PROCEDURE proc_cursor_getcountofthetable(table_name STRING)

RETURNS INT

LANGUAGE SQL

AS

$$

DECLARE

  sql_text STRING;

  rs RESULTSET;

  row_count INT DEFAULT 0;

  C1 CURSOR FOR SELECT COUNT(*) FROM customer_src;

BEGIN

  OPEN C1;

  FETCH C1 INTO row_count;

  CLOSE C1;

  IF (row_count > 4) THEN

    RETURN 'Success';

  ELSE

    RETURN 'Failed';

  END IF;

END;

$$;

CALL proc_cursor_getcountofthetable('customer_src');

8. Stored Procedure with Execute Immediate & Dynamic SQL:

CREATE OR REPLACE PROCEDURE proc_with_ex_immediate_dynamic_sql(

    minimum_price NUMBER(12,2),

    maximum_price NUMBER(12,2))

  RETURNS TABLE (order_id NUMBER(38,0), SALES_REGION VARCHAR, ORDER_DATE DATE, amount NUMBER(38, 0))

--Give all the columns from the table, with exact data type and sequence  

  LANGUAGE SQL

AS

$$

DECLARE

  rs RESULTSET;

  query VARCHAR DEFAULT 'SELECT * FROM SALES WHERE amount > ? AND amount < ?';

BEGIN

  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));

  RETURN TABLE(rs);

END;

$$

;

CALL proc_with_ex_immediate_dynamic_sql(1000,1500);