Stored Procedures

         

            Typically performs database operations, usually administrative operations like INSERT, UPDATE or DELETE.

Doesn't need to return value.

Supported languages are,

• Snowflake scripting (Snowflake SQL + procedural logic)
• Javascript
• Snowpark API (Python, Scala & Java)

General Syntax:

CREATE PROCEDURE proc_name (table_name varchar ,n1 int)

EXECUTE as caller 

RETURN int

LANGUAGE sql

AS

BEGIN

UPDATE IDENTIFIER(:table_name) SET col_name = :n1;

END;

NOTE: 

• If argument is used in sql statement to refer to as an object then use IDENTIFIER(:argument)
• If argument is used in sql statement then use :argument

PRIVILEGES:

• Runs either with caller's or owner's rights.
• By default, will run with owner's privileges

UDF - User Defined Functions

Typically calculate and return a value.

Supported  Languages:

• SQL
• Python
• Java
• Javascript

Scalar functions: Returns one output row per input row

Tabular functions: Returns a tabular value for each row

        

Function is a securable-schema level object 

 

General syntax for function:

CREATE FUNCTION (n int)

returns int

AS

$$

n+2;

$$;

Approximation of the most frequent values

 Syntax: 

APPROX_TOP_K( expr, k, counters )

Arg1: the column name for which you want to find the most common values.

Arg2: if you want to see the top 10 most common values, then set k to 10

Arg3: Max no.of distinct values that can be tracked

 

Normal method to get most frequent value from the table,

    SELECT customer_id, COUNT(customer_id) FROM table_name GROUP BY 1;

Using estimation function,

    SELECT APPROX_TOP_K(customer_id,5,20) FROM table_name;

Benchmark: For 150,000,000 rows

•     Normal method took, 12s
•     Estimation function took, 5.6s

Also check for:

APPROX_TOP_K_ACCUMULATE , 

APPROX_TOP_K_COMBINE, 

APPROX_TOP_K_ESTIMATE

Estimating the Number of Distinct Values

Normal method to get the distinct values,

    SELECT COUNT(DISTINCT(customer_name)) FROM table_name;

Using estimation function,

    SELECT HLL(customer_name) FROM table_name;

Benchmark: For 150,000,000 rows

•     Normal method took, 12s
•     Estimation function took, 5.6s

Stage and Copy - Useful Commands

To show the list of stages: 

• SHOW STAGES;
• DESC STAGE name_of_the_stage;

To show the list of file formats : 

• SHOW FILE FORMATS;
• DESC FILE FORMAT name_of_the_file_format;

Sample file format creation :

CREATE OR REPLACE FILE FORMAT emp_csv_format

  TYPE = 'CSV'

  FIELD_DELIMITER = ','

  SKIP_HEADER = 1

  FIELD_OPTIONALLY_ENCLOSED_BY = '"'

  NULL_IF = ('NULL', 'null');

Sample file format alter :

ALTER FILE FORMAT emp_csv_format

SET FIELD_DELIMITER = '|';

LIST @emp_named_stage;

Sample stage creation :

CREATE OR REPLACE STAGE emp_named_stage

  FILE_FORMAT = emp_csv_format;

Read data from the stage :

SELECT

        METADATA$FILENAME,

        METADATA$FILE_ROW_NUMBER,

        $1 AS column1_name, -- Referencing the first column

        $2 AS column2_name,  -- Referencing the second column

        $3 AS Age,

        $4 AS City,

        $5 AS unknowncolumn

    FROM @emp_named_stage (file_format => emp_csv_format) AS t;

Copy the data from stage to table :

COPY INTO emp_data FROM @emp_named_stage/samplecsv_error.csv ON_ERROR='continue';

Remove the file from stage :

REMOVE @emp_named_stage/samplecsv_error1.csv;

Validation mode :

COPY INTO emp_data

FROM @emp_named_stage/samplecsv_error1.csv

FILE_FORMAT = emp_csv_format

VALIDATION_MODE = 'RETURN_ERRORS';

To check the history of file load :

SELECT * FROM DIRECTORY(@name_of_the_stage);

Stored Procedure - Language Selection SQL vs Javascript

 Choosing between LANGUAGE JAVASCRIPT and LANGUAGE SQL (Snowflake Scripting) for stored procedures depends on what you’re trying to accomplish.

Use SQL scripting when:

• You're mostly executing SQL statements.

• You want clean, readable procedures.

• You're doing DML (INSERT, UPDATE, DELETE).

Use JavaScript when:

• You need complex procedural logic not easily expressed in SQL.

• You want to build dynamic SQL more flexibly.

• You want to loop through result sets or arrays.

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);