Snowflake Scripting UDFs: A Developer

Remember when you had to switch to Javascript (LANGUAGE JAVASCRIPT) just to write a simple IF/ELSE block in a Stored Procedure? Those dark days are over.

Snowflake Scripting allows you to write procedural logic (loops, branches, variable assignment) using standard SQL syntax. It makes PL/SQL (Oracle) developers feel right at home.

1. The Block Structure#

Everything happens inside a BEGIN ... END block.

EXECUTE IMMEDIATE $$
DECLARE
    profit number(10, 2);
    revenue number(10, 2);
    cost number(10, 2);
BEGIN
    SELECT SUM(amt) INTO :revenue FROM sales;
    SELECT SUM(amt) INTO :cost FROM expenses;

    profit := revenue - cost;

    RETURN profit;
END;
$$;

2. Looping with Cursors#

Iterating over rows is a common pattern for complex migrations (though set-based SQL is always faster if possible).

DECLARE
    c1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'PUBLIC';
    tbl_name VARCHAR;
BEGIN
    OPEN c1;
    FOR record IN c1 DO
        tbl_name := record.table_name;
        -- Dynamic SQL execution
        EXECUTE IMMEDIATE 'GRANT SELECT ON TABLE ' || tbl_name || ' TO ROLE analyst';
    END FOR;
    RETURN 'Grants applied';
END;

3. Exception Handling#

Robust code needs error handling.

BEGIN
    INSERT INTO my_table VALUES (1);
EXCEPTION
    WHEN OTHER THEN
        RETURN 'Error occurred: ' || SQLCODE || ' - ' || SQLERRM;
END;

4. Best Practices#

• Bind Variables: Always use :variable syntax to prevent SQL injection in dynamic strings.
• Don’t Overuse: Loops in SQL are slow compared to native set operations. Only use Scripting for control flow ( orchestration), not for heavy data transformation.

Conclusion#

Snowflake Scripting closes the functionality gap with legacy RDBMS procedural languages. It simplifies migration and makes code more readable for pure SQL practitioners.