How to Create Stored Procedures in Snowflake

What does a Snowflake stored procedure do?

It executes multiple SQL statements and JavaScript expressions as a single programmable unit, returning a value when finished. Use it for complex data transformations, auditing, or conditional logic.

Why choose a stored procedure over a view or script?

Procedures support variables, loops, error handling, and versioning, enabling advanced ETL logic that a view cannot perform and centralizing logic that ad-hoc scripts scatter.

How do I create a stored procedure?

Use the CREATE PROCEDURE command with a JavaScript handler.Define input parameters, return type, language (javascript or sql), and the executable body.

Example

CREATE OR REPLACE PROCEDURE process_daily_orders(proc_date DATE)
RETURNS STRING
LANGUAGE javascript
EXECUTE AS owner
AS $$
var sql_cmd = `
INSERT INTO Orders_Audit
SELECT * FROM Orders WHERE order_date = '` + proc_date + `';
`;
snowflake.execute({sqlText: sql_cmd});
return 'Audit complete for ' + proc_date;
$$;

How do I call a procedure?

Invoke with the CALL statement.Example: CALL process_daily_orders('2023-08-01');

How can I modify an existing procedure?

Use CREATE OR REPLACE PROCEDURE with the same name or use ALTER PROCEDURE … SET TAG for metadata updates.

Best practices for stored procedures

• Keep procedures single-purpose.
• Use EXECUTE AS OWNER to avoid permission errors.
• Log actions to an audit table.
• Version control scripts outside Snowflake.

Common use case: recalculating customer lifetime value (CLV)

Wrap a complex CLV computation that touches Customers, Orders, OrderItems, and Products in a single procedure so analysts can simply CALL it.

.