Snowflake Advanced Stored Procedures

Advanced stored procedures in Snowflake enable complex business logic implementation with error handling, dynamic SQL, transactions, and optimized performance patterns.

What are Advanced Stored Procedures?

• Implement complex business logic in SQL, JavaScript, or Python
• Use cursors, loops, and conditional branching
• Control transactions with COMMIT/ROLLBACK and savepoints

Architecture Overview

The advanced stored procedure architecture includes three layers:

1. Input Layer — Parameters, table functions, caller context, session variables, bind variables
2. Stored Procedure Engine

1. SQL Execution — Static SQL, dynamic SQL, EXECUTE IMMEDIATE, result sets, transactions

Advanced Patterns

• Cursor Pattern — Row-by-row processing
• Dynamic SQL — Schema-agnostic execution
• Transaction Control — BEGIN TRANSACTION, COMMIT/ROLLBACK, savepoints
• Performance — Batch processing, bulk operations, result caching, parallel execution

Stored Procedure Patterns

Basic Procedure with Error Handling

CREATE OR REPLACE PROCEDURE process_data(table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  row_count INTEGER;
  error_msg VARCHAR;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || table_name INTO row_count;
  
  EXECUTE IMMEDIATE '
    INSERT INTO target_table
    SELECT * FROM ' || table_name || ' WHERE processed = FALSE
  ';
  
  RETURN 'Successfully processed ' || :row_count || ' rows';
  
EXCEPTION
  WHEN OTHER THEN
    error_msg := 'Error processing ' || table_name || ': ' || SQLERRM;
    INSERT INTO error_log (procedure_name, error_message, timestamp)
    VALUES ('process_data', :error_msg, CURRENT_TIMESTAMP());
    RAISE;
END;

Cursor-Based Processing

CREATE OR REPLACE PROCEDURE cursor_processing()
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  cursor1 CURSOR FOR SELECT id, name FROM source_table;
  id INTEGER;
  name VARCHAR;
BEGIN
  OPEN cursor1;
  
  FOR record IN cursor1 DO
    id := record.id;
    name := record.name;
    
    INSERT INTO target_table (id, name, processed_at)
    VALUES (:id, :name, CURRENT_TIMESTAMP());
  END FOR;
  
  CLOSE cursor1;
  
  RETURN 'Cursor processing complete';
END;

Dynamic SQL Pattern

CREATE OR REPLACE PROCEDURE dynamic_processing(schema_name VARCHAR, table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  sql_stmt VARCHAR;
  row_count INTEGER;
BEGIN
  sql_stmt := 'SELECT COUNT(*) FROM ' || schema_name || '.' || table_name;
  EXECUTE IMMEDIATE :sql_stmt INTO row_count;
  
  IF (:row_count > 1000) THEN
    sql_stmt := 'INSERT INTO target SELECT * FROM ' || schema_name || '.' || table_name || ' LIMIT 1000';
  ELSE
    sql_stmt := 'INSERT INTO target SELECT * FROM ' || schema_name || '.' || table_name;
  END IF;
  
  EXECUTE IMMEDIATE :sql_stmt;
  
  RETURN 'Processed ' || :row_count || ' rows from ' || table_name;
END;

Transaction Control

CREATE OR REPLACE PROCEDURE transaction_example()
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  BEGIN TRANSACTION;
  
  INSERT INTO table_a (id, data) VALUES (1, 'test');
  
  SAVEPOINT sp1;
  
  INSERT INTO table_b (id, data) VALUES (1, 'test');
  
  COMMIT;
  
  RETURN 'Transaction completed successfully';
EXCEPTION
  WHEN OTHER THEN
    ROLLBACK TO SAVEPOINT sp1;
    INSERT INTO error_log (error_message) VALUES (SQLERRM);
    RETURN 'Transaction failed: ' || SQLERRM;
END;

Performance Optimization

CREATE OR REPLACE PROCEDURE batch_processing(batch_size INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  total_rows INTEGER;
  processed INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO total_rows FROM source WHERE processed = FALSE;
  
  WHILE processed < total_rows DO
    INSERT INTO target
    SELECT * FROM source
    WHERE processed = FALSE
    LIMIT :batch_size;
    
    UPDATE source SET processed = TRUE
    WHERE id IN (SELECT id FROM target ORDER BY id DESC LIMIT :batch_size);
    
    processed := processed + :batch_size;
  END WHILE;
  
  RETURN 'Processed ' || :processed || ' rows in batches of ' || :batch_size;
END;

Procedure Comparison