CW

Snowflake Advanced Stored Procedures

Free Lesson

Advertisement

Snowflake Advanced Stored Procedures

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

Architecture Overview

<svg width="800" height="450" viewBox="0 0 800 450" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="procGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#1ABC9C;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#48C9B0;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Advanced Stored Procedure Architecture</text>

  <rect x="30" y="60" width="180" height="150" rx="10" fill="#6C5CE7" opacity="0.9"/>
  <text x="120" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Input Layer</text>
  <text x="120" y="110" text-anchor="middle" font-size="10" fill="white">Parameters</text>
  <text x="120" y="125" text-anchor="middle" font-size="10" fill="white">Table Functions</text>
  <text x="120" y="140" text-anchor="middle" font-size="10" fill="white">Caller Context</text>
  <text x="120" y="155" text-anchor="middle" font-size="10" fill="white">Session Variables</text>
  <text x="120" y="175" text-anchor="middle" font-size="10" fill="white">Bind Variables</text>

  <path d="M210 135 L250 135" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowProc)"/>

  <rect x="250" y="60" width="300" height="150" rx="10" fill="url(#procGrad)" opacity="0.9"/>
  <text x="400" y="85" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Stored Procedure Engine</text>

  <rect x="270" y="100" width="85" height="95" rx="5" fill="white" opacity="0.9"/>
  <text x="312" y="120" text-anchor="middle" font-size="9" fill="#333" font-weight="bold">Variables</text>
  <text x="312" y="135" text-anchor="middle" font-size="8" fill="#666">DECLARE</text>
  <text x="312" y="148" text-anchor="middle" font-size="8" fill="#666">LET</text>
  <text x="312" y="161" text-anchor="middle" font-size="8" fill="#666">SET</text>
  <text x="312" y="174" text-anchor="middle" font-size="8" fill="#666">Cursors</text>
  <text x="312" y="187" text-anchor="middle" font-size="8" fill="#666">Arrays</text>

  <rect x="365" y="100" width="85" height="95" rx="5" fill="white" opacity="0.9"/>
  <text x="407" y="120" text-anchor="middle" font-size="9" fill="#333" font-weight="bold">Control</text>
  <text x="407" y="135" text-anchor="middle" font-size="8" fill="#666">IF/ELSE</text>
  <text x="407" y="148" text-anchor="middle" font-size="8" fill="#666">CASE</text>
  <text x="407" y="161" text-anchor="middle" font-size="8" fill="#666">FOR/WHILE</text>
  <text x="407" y="174" text-anchor="middle" font-size="8" fill="#666">LOOP</text>
  <text x="407" y="187" text-anchor="middle" font-size="8" fill="#666">RETURN</text>

  <rect x="460" y="100" width="80" height="95" rx="5" fill="white" opacity="0.9"/>
  <text x="500" y="120" text-anchor="middle" font-size="9" fill="#333" font-weight="bold">Error</text>
  <text x="500" y="135" text-anchor="middle" font-size="8" fill="#666">BEGIN</text>
  <text x="500" y="148" text-anchor="middle" font-size="8" fill="#666">EXCEPTION</text>
  <text x="500" y="161" text-anchor="middle" font-size="8" fill="#666">WHEN</text>
  <text x="500" y="174" text-anchor="middle" font-size="8" fill="#666">SQLCODE</text>
  <text x="500" y="187" text-anchor="middle" font-size="8" fill="#666">SQLERRM</text>

  <path d="M550 135 L590 135" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowProc)"/>

  <rect x="590" y="60" width="180" height="150" rx="10" fill="#3498DB" opacity="0.9"/>
  <text x="680" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">SQL Execution</text>
  <text x="680" y="110" text-anchor="middle" font-size="10" fill="white">Static SQL</text>
  <text x="680" y="125" text-anchor="middle" font-size="10" fill="white">Dynamic SQL</text>
  <text x="680" y="140" text-anchor="middle" font-size="10" fill="white">EXECUTE IMMEDIATE</text>
  <text x="680" y="155" text-anchor="middle" font-size="10" fill="white">Result Sets</text>
  <text x="680" y="175" text-anchor="middle" font-size="10" fill="white">Transactions</text>

  <rect x="30" y="230" width="740" height="200" rx="10" fill="#27AE60" opacity="0.85"/>
  <text x="400" y="255" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Advanced Patterns</text>

  <rect x="50" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="135" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Cursor Pattern</text>
  <text x="135" y="315" text-anchor="middle" font-size="9" fill="#666">DECLARE cursor CURSOR</text>
  <text x="135" y="330" text-anchor="middle" font-size="9" fill="#666">FOR SELECT FROM table</text>
  <text x="135" y="345" text-anchor="middle" font-size="9" fill="#666">OPEN cursor</text>
  <text x="135" y="360" text-anchor="middle" font-size="9" fill="#666">FETCH INTO variable</text>
  <text x="135" y="375" text-anchor="middle" font-size="9" fill="#666">CLOSE cursor</text>

  <rect x="240" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="325" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Dynamic SQL</text>
  <text x="325" y="315" text-anchor="middle" font-size="9" fill="#666">EXECUTE IMMEDIATE</text>
  <text x="325" y="330" text-anchor="middle" font-size="9" fill="#666">Parameterized queries</text>
  <text x="325" y="345" text-anchor="middle" font-size="9" fill="#666">Schema-aware logic</text>
  <text x="325" y="360" text-anchor="middle" font-size="9" fill="#666">Runtime query building</text>

  <rect x="430" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="515" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Transaction Control</text>
  <text x="515" y="315" text-anchor="middle" font-size="9" fill="#666">BEGIN TRANSACTION</text>
  <text x="515" y="330" text-anchor="middle" font-size="9" fill="#666">COMMIT / ROLLBACK</text>
  <text x="515" y="345" text-anchor="middle" font-size="9" fill="#666">Savepoints</text>
  <text x="515" y="360" text-anchor="middle" font-size="9" fill="#666">Error recovery</text>

  <rect x="620" y="275" width="140" height="130" rx="8" fill="white"/>
  <text x="690" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Performance</text>
  <text x="690" y="315" text-anchor="middle" font-size="9" fill="#666">Batch processing</text>
  <text x="690" y="330" text-anchor="middle" font-size="9" fill="#666">Bulk operations</text>
  <text x="690" y="345" text-anchor="middle" font-size="9" fill="#666">Result caching</text>
  <text x="690" y="360" text-anchor="middle" font-size="9" fill="#666">Parallel execution</text>

  <defs>
    <marker id="arrowProc" markerWidth="10" markerHeight="10" refX="9" refY="3" orient="auto" markerUnits="strokeWidth">
      <path d="M0,0 L0,6 L9,3 z" fill="#333"/>
    </marker>
  </defs>
</svg>

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;

Use EXECUTE IMMEDIATE for dynamic SQL. Always use bind variables (colon prefix) to prevent SQL injection. Consider using RESULTSET for returning multiple rows from procedures.

Procedure Comparison

FeatureSQLJavaScriptPython
Error HandlingTRY/CATCHtry/catchtry/except
Dynamic SQLEXECUTE IMMEDIATEsnowflake.executecursor.execute
External CallsLimitedHTTP supportFull libraries
PerformanceGoodBetter for logicBest for ML
ComplexitySimpleMediumHigh
  • SQL stored procedures support cursors, loops, and error handling
  • Dynamic SQL enables schema-agnostic processing
  • Transaction control ensures data consistency
  • Batch processing optimizes large dataset operations
  • JavaScript and Python offer advanced capabilities

Advertisement

←45 Snowflake Tasks Advanced47 Snowflake Udf Advanced→

Need Expert Snowflake Help?

Get personalized warehouse optimization, data modeling, or Snowflake platform consulting.

Contact Us →View Services

Advertisement