Snowflake Stored Procedures

You are a Snowflake database engineer building reusable JavaScript and SQL stored procedures. The user wants to create, deploy, and execute stored procedures that combine procedural logic with SQL operations in Snowflake.

What to check first

• Run SHOW PROCEDURES; in Snowflake to see existing procedures and naming conventions
• Verify you have the CREATE PROCEDURE privilege on the target schema
• Check Snowflake account region and warehouse availability with SELECT CURRENT_WAREHOUSE(), CURRENT_REGION();

Steps

1. Create a Snowflake connection using SnowflakeSDK or SQL editor authenticated to your target database and schema
2. Write the JavaScript stored procedure with CREATE OR REPLACE PROCEDURE statement, specifying input parameters with types (VARCHAR, NUMBER, ARRAY, OBJECT)
3. Set procedure language to LANGUAGE JAVASCRIPT and execution context with EXECUTE AS OWNER or EXECUTE AS CALLER
4. Use snowflake.execute() to run SQL commands from within the procedure, passing parameterized queries with ? placeholders
5. Define return type using RETURNS clause (VARCHAR, TABLE, OBJECT) — use TABLE(col_name VARCHAR, ...) for multi-column results
6. Handle errors with try-catch blocks and return error messages or throw exceptions with throw new Error()
7. Deploy the procedure using PUT command or execute the CREATE statement directly in Snowflake UI
8. Test the procedure with CALL procedure_name(arg1, arg2); and verify output matches expected structure

Code

// JavaScript Stored Procedure in Snowflake
CREATE OR REPLACE PROCEDURE sp_process_orders(order_date VARCHAR, min_amount NUMBER)
RETURNS TABLE(order_id NUMBER, customer_name VARCHAR, total_amount NUMBER, processing_status VARCHAR)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
  let result_rows = [];
  
  try {
    // Parse input date parameter
    const query_date = new Date(order_date);
    
    // Execute SQL query to fetch orders
    const fetch_stmt = snowflake.createStatement({
      sqlText: `SELECT order_id, customer_name, total_amount 
                FROM orders 
                WHERE order_date >= ?::DATE 
                AND total_amount > ? 
                ORDER BY order_date DESC`,
      binds: [order_date, min_amount]
    });
    
    const fetch_result = fetch_stmt.execute();
    
    // Process each row with business logic
    while (fetch_result.next()) {
      const order_id = fetch_result.getColumnValue(1);
      const customer_name = fetch_result.getColumnValue(2);
      const total_amount = fetch_result.getColumnValue(3);
      
      // Calculate processing fee (5% of amount)
      const processing_fee = total_amount * 0.05;
      const final_amount = total_amount

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change