Brolly Academy | Snowflake
Stored Procedures in Snowflake
Syntax, Examples, Caller vs Owner Rights, Errors and Tasks (2026)
A plain-English guide to stored procedures in Snowflake for October 2026: what they are, which languages you can use, the CREATE PROCEDURE syntax, short examples in Snowflake Scripting, JavaScript and Python, arguments and return values, caller's vs owner's rights, error handling, scheduling with tasks, how procedures differ from UDFs, and the mistakes beginners make most.
Stored procedures in Snowflake for beginners: CREATE PROCEDURE syntax, Snowflake Scripting, JavaScript and Python examples, caller vs owner rights.
By Brolly Academy Team · Brolly Academy · Updated 24 October 2026

Table of Contents
1. Stored procedures in Snowflake at a glance
2. What is a stored procedure in Snowflake?
3. Which languages can you use for Snowflake stored procedures?
4. CREATE PROCEDURE syntax explained
5. Example 1: a Snowflake Scripting procedure with arguments
6. Example 2: loops, variables and returning a table
7. Example 3: a JavaScript stored procedure
8. Example 4: a Python stored procedure
9. Arguments and return values
10. How to call a stored procedure in Snowflake
11. Caller's rights vs owner's rights
12. Error handling in Snowflake stored procedures
13. Calling stored procedures from tasks
14. Stored procedures vs UDFs in Snowflake
15. Real use cases for stored procedures
16. Best practices for Snowflake stored procedures
17. Common errors and how to fix them
Quick answer
A stored procedure in Snowflake is a named, reusable block of code that you create once with CREATE PROCEDURE and run with CALL, so you can execute many SQL statements, with loops, conditions and error handling, as a single step. You can write the logic (Snowflake calls it the handler) in Snowflake Scripting (SQL), JavaScript, Python, Java or Scala, according to the Snowflake stored procedures overview (Snowflake documentation, October 2026). Procedures are best for admin and pipeline work such as loading, merging, purging and auditing data. If you need a value inside a SELECT, use a UDF (user-defined function) instead.
- Create: CREATE OR REPLACE PROCEDURE name(args) RETURNS type LANGUAGE ... AS $$ ... $$
- Run: CALL name(values);
- Languages: Snowflake Scripting, JavaScript, Python, Java, Scala
- Security: runs with owner's rights by default; add EXECUTE AS CALLER for caller's rights
- Automation: call a procedure from a task to run it on a schedule
Key takeaways
- A stored procedure groups many SQL statements and procedural logic into one object that you run with CALL, which makes repeated multi-step jobs easy to reuse and automate.
- Snowflake Scripting and JavaScript handlers must be written in-line; Python, Java and Scala handlers can be in-line or staged as files.
- Procedures run with owner's rights unless you write EXECUTE AS CALLER, and that one choice changes which privileges, session variables and context the code can see.
- Snowflake Scripting has DECLARE, BEGIN, EXCEPTION blocks, loops and named exceptions, so you can catch and report errors cleanly instead of failing silently.
- Use a procedure for DDL and DML work; use a UDF when you need a value inside a SQL statement. Schedule procedures with tasks rather than running them by hand.
1. Stored procedures in Snowflake at a glance
Short answer: This table gives you the whole topic on one screen, so you can jump to the part you need.
Every row below comes from Snowflake's own documentation, read in October 2026. Each later section explains one row in detail with a simplified example.
If you are new to Snowflake itself, read our guide on what Snowflake does first, then come back here.
2. What is a stored procedure in Snowflake?
Short answer: A stored procedure is saved code inside a Snowflake database that runs a sequence of steps, such as several INSERT, MERGE, DELETE or CREATE statements, with branching and looping, whenever you call it.
Real data work rarely fits in one statement. A nightly load might copy data, merge it into a target table, delete old rows and write an audit record. A stored procedure puts all of that in one named object that you run with a single CALL.
The Snowflake overview page (October 2026) lists the main reasons to use stored procedures:
- Procedural logic: add IF, CASE and loops on top of SQL
- Reuse: call the same logic from scripts, tasks or other procedures
- Automation: wrap repeated multi-step database work into one object
- Dynamic SQL: build a SQL string at run time and execute it
- Controlled access: let users run a job with the owner's privileges instead of their own
A few terms you will see throughout this post:
- Handler: the code that does the work inside the procedure
- Snowflake Scripting: Snowflake's procedural extension to SQL, with variables, blocks, loops and exceptions
- UDF (user-defined function): a function you write that returns a value inside a SQL statement
- Task: a Snowflake object that runs SQL or calls a procedure on a schedule or when a stream has data
- Stream: an object that tracks inserts, updates and deletes on a table so you can process only the changes
- Virtual warehouse: the compute cluster that runs your queries; it uses credits, the unit Snowflake bills compute in
- RBAC (role-based access control): privileges are granted to roles, and roles are granted to users
If you came from Oracle PL/SQL, SQL Server T-SQL or Teradata, the idea is familiar. The syntax and the security model are different, which is why most of this guide focuses on those two areas.
3. Which languages can you use for Snowflake stored procedures?
Short answer: You can write the handler in Snowflake Scripting (SQL), JavaScript, Python, Java or Scala; the language you pick decides where the code lives and whether you can return a table.
The CREATE PROCEDURE reference (Snowflake documentation, October 2026) sets out these differences.
How to choose, in practice:
- Start with Snowflake Scripting if your job is mostly SQL. It reads like the SQL you already know and needs no extra packages.
- Choose Python when you need DataFrame logic or Python packages. Python procedures use Snowpark, Snowflake's library for running Python, Java and Scala code inside Snowflake. Our post on Snowpark in Snowflake covers the API itself.
- Read JavaScript confidently, because many existing Snowflake projects still use it, even if you write new procedures in SQL or Python.
- Pick Java or Scala only when your team already works in them.
Job descriptions back up this mix. Of 14 Snowflake job descriptions we read on Naukri and LinkedIn on 25 October 2026, TCS, DTCC and Capgemini named stored procedures or UDFs, and an EPAM Systems role asked for Snowflake Scripting and Snowpark for Python. Our guide to the Snowflake developer role covers the rest of those skills.
4. CREATE PROCEDURE syntax explained
Short answer: Every procedure has a name with typed arguments, a RETURNS clause, a LANGUAGE clause and a body between $$ markers; optional clauses control security, null handling and packages.
Here is the anatomy of a Snowflake Scripting procedure, taken from the docs pattern.
-- Simplified example: the parts of a procedure
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$;
CALL output_message('Hello World');
What each clause does, from the CREATE PROCEDURE reference (October 2026):
Two more points from the reference that save beginners time:
- Snowflake does not fully validate the code when you create the procedure. A typo in a table name may only show up as an error the first time you CALL it. Always run a test CALL right after creating it.
- Procedures are not atomic. If statement five fails, statements one to four are not automatically rolled back. Plan your own transaction handling and error logging.
You can also create a TEMPORARY procedure that lasts only for your session. The overview page notes that temporary and anonymous procedures do not need the CREATE PROCEDURE privilege on the schema.
5. Example 1: a Snowflake Scripting procedure with arguments
Short answer: In Snowflake Scripting, you read an argument directly in procedural code, but inside a SQL statement you put a colon in front of it, as in :job_name.
This first real example writes an audit row every time a job runs. Audit logging is one of the most common procedures in Indian GCC and IT services projects, because support teams need to know what ran and when.
-- Simplified example: log a job run
CREATE OR REPLACE PROCEDURE log_run(job_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO job_audit (job_name, run_at)
VALUES (:job_name, CURRENT_TIMESTAMP());
RETURN 'Logged ' || job_name;
END;
$$;
CALL log_run('daily_sales_load');
What to notice:
- :job_name inside INSERT. Snowflake's Snowflake Scripting procedure docs (October 2026) say you prefix an argument with a colon when you use it inside a SQL statement.
- job_name without a colon in RETURN. Outside SQL statements, in expressions and assignments, you use the plain name.
- BEGIN ... END is required. DECLARE and EXCEPTION are optional parts of the block.
The structure of every Snowflake Scripting block is the same:
If you only want to test some logic without creating an object, you can run an anonymous block. In Snowsight you can run the block directly; in Snowflake CLI, the Classic Console or the Python connector, wrap it in EXECUTE IMMEDIATE $$ ... $$, as the Snowflake Scripting blocks page explains (October 2026).
6. Example 2: loops, variables and returning a table
Short answer: Snowflake Scripting supports FOR, WHILE, REPEAT and LOOP, and a procedure can return a full result set with RETURNS TABLE and RETURN TABLE(res).
First, a loop. This procedure adds the numbers from 1 to n. It is a toy, but the same FOR pattern drives real jobs, such as processing a list of regions or months one by one.
-- Simplified example: a counter loop
CREATE OR REPLACE PROCEDURE sum_to_n(n INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
total INTEGER DEFAULT 0;
BEGIN
FOR i IN 1 TO n DO
total := total + i;
END FOR;
RETURN total;
END;
$$;
CALL sum_to_n(5);
The loop types, from the Snowflake Scripting loops page (October 2026):
BREAK (or EXIT) leaves a loop, and CONTINUE (or ITERATE) skips to the next pass.
Now a procedure that returns a table. Searchers often ask "can a Snowflake stored procedure return a table?" The answer is yes for Snowflake Scripting, Python, Java and Scala.
-- Simplified example: return the top 10 sales days
CREATE OR REPLACE PROCEDURE top_sales_days()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity
FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
$$;
CALL top_sales_days();
A RESULTSET is a variable that holds the result of a query. You wrap it in TABLE() when you return it. The CREATE PROCEDURE reference (October 2026) adds that you can leave the column list empty, RETURNS TABLE (), when the columns are only known at run time.
7. Example 3: a JavaScript stored procedure
Short answer: A JavaScript procedure runs SQL through the snowflake object, using createStatement with sqlText and binds, and it must refer to arguments in uppercase inside the JavaScript code.
You will meet JavaScript procedures in many existing projects, so you should be able to read and fix them.
-- Simplified example: count orders with a given status
CREATE OR REPLACE PROCEDURE count_orders(status VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
var stmt = snowflake.createStatement({
sqlText: "SELECT COUNT(*) FROM orders WHERE status = ?",
binds: [STATUS]
});
var rs = stmt.execute();
rs.next();
return rs.getColumnValue(1);
$$;
CALL count_orders('SHIPPED');
Rules from Snowflake's JavaScript stored procedures guide (Snowflake documentation, October 2026):
- Uppercase arguments. SQL is case-insensitive, but JavaScript is case-sensitive. An argument declared as status in SQL is STATUS inside the JavaScript code. Writing status in lowercase is the most common JavaScript procedure bug. The docs' other fix is to double-quote the argument name in SQL so it keeps its case.
- Use binds, not string joins. The ? placeholder with binds passes values safely. Only JavaScript numbers, strings and SfDate values can be bound.
- Read results row by row. Call next() to move to a row and getColumnValue(1) to read the first column.
- One return value only. A JavaScript procedure returns a single value. For more data, return a VARIANT or a delimited string, or switch to Snowflake Scripting or Python if you need RETURNS TABLE.
Should you write new procedures in JavaScript or Snowflake Scripting? For SQL-heavy work, Snowflake Scripting is usually easier to read and lets you return tables. JavaScript remains fully supported, so there is no need to rewrite working code just for the sake of it.
8. Example 4: a Python stored procedure
Short answer: A Python procedure needs RUNTIME_VERSION, the snowflake-snowpark-python package and a HANDLER function whose first argument is a Snowpark Session that Snowflake passes in for you.
This example is the in-line Python handler pattern from the CREATE PROCEDURE reference. It copies the first N rows of one table into another.
-- Simplified example: copy N rows with Snowpark
CREATE OR REPLACE PROCEDURE copy_top_rows(
from_table STRING, to_table STRING, count INT)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
AS
$$
def run(session, from_table, to_table, count):
session.table(from_table).limit(count).write.save_as_table(to_table)
return "SUCCESS"
$$;
CALL copy_top_rows('orders', 'orders_sample', 100);
Key rules from Snowflake's Python procedure pages (October 2026):
- Session comes first. The handler's first parameter is the Snowpark Session. Snowflake creates it when you call the procedure; you cannot create it yourself, and you do not list it as a SQL argument.
- Runtime versions. Python 3.10 to 3.14 are generally available; 3.9 is deprecated.
- Snowpark package is required. Include snowflake-snowpark-python in PACKAGES.
- HANDLER names the function. For in-line code, give just the function name; for staged code, give module_name.function_name.
- Uncaught exceptions fail the call with an error that includes a Python stack trace.
- Async child jobs. If you start child jobs with asynchronous calls such as collect_nowait, they are cancelled when the parent job finishes first.
For DataFrames, lazy evaluation and the wider Snowpark API, see our guide to Snowpark in Snowflake. This post stays on procedure syntax.
9. Arguments and return values
Short answer: Arguments are typed in the CREATE statement, a procedure is identified by its name plus argument types, and RETURNS can be a single value or a table.
Here are the rules that cause the most confusion, all from the CREATE PROCEDURE reference (October 2026):
Dynamic SQL deserves a short example, because the colon rule flips. When you build a SQL string, you join the plain argument name into the string, then run it with EXECUTE IMMEDIATE. The Snowflake Scripting docs show this pattern:
-- Simplified example: dynamic SQL inside a procedure body
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
Be careful with dynamic SQL built from user input. Joining raw text into SQL can open the door to SQL injection. Validate inputs, or use bind variables where the language supports them, as in the JavaScript example.
Overloading also explains a common error. Because a procedure is identified by name and argument types, ALTER, DROP and GRANT statements must include the argument types, for example DROP PROCEDURE log_run(VARCHAR). Leave the types out and Snowflake says the procedure does not exist.
10. How to call a stored procedure in Snowflake
Short answer: You run a procedure with CALL proc_name(args); you can also call one procedure from another, capture its result in a variable, or create and call a one-time anonymous procedure.
The ways to call a procedure, from the Snowflake overview and Snowflake Scripting pages (October 2026):
- From a worksheet or script: CALL myproc('table_a', 'table_b', 5);
- From another Snowflake Scripting procedure: pass variables with a colon, such as CALL insert_value(:value1);
- Capture the result: assign a call to a RESULTSET, res1 := (CALL my_procedure());, then loop over it with a cursor
- From a task: a task body can be CALL my_proc(); on a schedule (section 13)
- Anonymous procedure: create and call a procedure in one statement; Snowflake drops it right after
- From any client: Snowflake CLI, drivers and connectors send the CALL statement like any other SQL
Remember one limit from the stored procedures vs UDFs page (Snowflake documentation, October 2026): you can call only one stored procedure per SQL statement. A procedure can call other procedures, and can even call itself recursively.
You cannot use a procedure's return value directly inside an expression like SELECT myproc(). The same page lists workarounds: capture it in a Snowflake Scripting variable, call it in a FROM clause if it returns a table, read it with RESULT_SCAN, or store the results in a table.
To see who can call your procedure, remember that calling needs the USAGE privilege on the procedure, plus access to its database and schema. Grant it to a role, never to individual users, in line with RBAC.
11. Caller's rights vs owner's rights
Short answer: An owner's rights procedure (the default) runs with the privileges of the role that owns it, while a caller's rights procedure (EXECUTE AS CALLER) runs with the privileges, context and session variables of the person calling it.
This is the most important design choice in a Snowflake procedure, and a favourite interview question. The caller's and owner's rights page (Snowflake documentation, October 2026) sets out the differences.
A simplified example of switching between the two:
-- Simplified example: caller's rights, then switch to owner's
CREATE OR REPLACE PROCEDURE show_my_role()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
RETURN CURRENT_ROLE();
END;
$$;
ALTER PROCEDURE show_my_role() EXECUTE AS OWNER;
How to choose:
- Use owner's rights when you want people to run a fixed, safe job (for example, purging rows older than 90 days) without giving them DELETE on the table. The procedure is the only door.
- Use caller's rights for helper procedures that should respect each user's own access, or that need to read or set session variables.
- Keep owner's rights procedures small and reviewed. They act with the owner's power, so avoid dynamic SQL built from unchecked input inside them.
There is also a third option, EXECUTE AS RESTRICTED CALLER. The CREATE PROCEDURE reference lists it as a preview feature: it runs like caller's rights but may not have all of the caller's privileges. Do not rely on it for production work until Snowflake marks it generally available.
12. Error handling in Snowflake stored procedures
Short answer: In Snowflake Scripting you declare named exceptions, RAISE them, and catch errors in an EXCEPTION block using SQLCODE, SQLERRM and SQLSTATE; JavaScript uses try/catch and Python uses normal exceptions.
Error handling is what separates a demo procedure from a production one. Without it, a failed nightly job may leave half-loaded data and no clear message.
-- Simplified example: named exception and handlers
CREATE OR REPLACE PROCEDURE check_stock(qty INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
low_stock EXCEPTION (-20010, 'Stock too low.');
BEGIN
IF (qty < 10) THEN
RAISE low_stock;
END IF;
RETURN 'Stock OK';
EXCEPTION
WHEN low_stock THEN RETURN 'Handled: ' || SQLERRM;
WHEN OTHER THEN RETURN 'Error ' || SQLCODE || ': ' || SQLERRM;
END;
$$;
What the Snowflake Scripting exceptions page (Snowflake documentation, October 2026) tells you:
In JavaScript, wrap your snowflake calls in try/catch. The error object carries code, state, message and stackTraceTxt, so you can return or log a full description:
// Simplified example: inside a JavaScript procedure body
try {
snowflake.execute({sqlText: "DELETE FROM stage_orders"});
return "Done";
} catch (err) {
return "Failed: " + err.code + " " + err.state + " " + err.message;
}
In Python, an unhandled exception fails the CALL with a stack trace. Catch the errors you expect and return or log a clear message.
A practical pattern: catch errors in the EXCEPTION block, insert a row into an error log table with SQLCODE, SQLERRM and the job name, and return a clear failure message, so support engineers can see what failed without reading the code.
13. Calling stored procedures from tasks
Short answer: A task can run CALL my_proc(); on a fixed schedule, on a cron schedule with a time zone, or when a stream has new data, which is how most production procedures run.
The CREATE TASK reference (Snowflake documentation, October 2026) includes a task whose body is a CALL. Here is the same pattern scheduled for 2 a.m. India time:
-- Simplified example: run a procedure every night at 2 a.m. IST
CREATE TASK nightly_audit_task
WAREHOUSE = mywh
SCHEDULE = 'USING CRON 0 2 * * * Asia/Kolkata'
AS
CALL log_run('nightly_load');
ALTER TASK nightly_audit_task RESUME;
What you need to know, from Snowflake's tasks documentation (October 2026):
- A new task starts suspended. Run ALTER TASK ... RESUME, or EXECUTE TASK for a single run.
- Schedules can be an interval such as '60 MINUTES' or 'USING CRON ...' with a time zone.
- Stream triggers. A task can run only when a stream has data, using WHEN SYSTEM$STREAM_HAS_DATA('stream_name').
- Compute choice. A user-managed task names a WAREHOUSE. A serverless task leaves WAREHOUSE out and needs the EXECUTE MANAGED TASK privilege.
- Task graphs. You can chain tasks in series or in parallel, so one procedure runs after another.
- Supported handlers. Tasks can call procedures written in JavaScript, Python, Java, Scala or Snowflake Scripting.
The classic pipeline is a stream on a raw table, a task that fires when the stream has data, and a stored procedure that merges the changes into a target table and logs the run. We cover streams, tasks, MERGE and recovery in depth in our Snowflake streams and tasks lab, so this post does not repeat it.
Cost note: a task that calls a procedure uses compute each time it runs, either your warehouse credits or serverless compute. Credit prices vary by region and edition, so check your own account's rates before you schedule anything to run every few minutes.
14. Stored procedures vs UDFs in Snowflake
Short answer: Use a stored procedure to do work (DDL, DML, multi-step jobs) with CALL; use a UDF to compute a value inside a SQL statement such as SELECT.
This is one of the most searched comparisons, and the stored procedures vs UDFs page (Snowflake documentation, October 2026) lays out the differences.
Choose a stored procedure when:
- you are migrating a procedure from Oracle, SQL Server or Teradata
- you need to create, update, delete or purge data, add users or drop temporary tables
- the job has several steps that must run in order
Choose a UDF when:
- you need a value for every row or group, such as a tax calculation or a cleaned-up phone number
- you want to call it inside SELECT, WHERE or JOIN
- you only need simple queries
The Snowflake UDF overview (October 2026) adds one more difference for data sharing: JavaScript and SQL UDFs can be shared through Secure Data Sharing, while Java, Python and Scala UDFs cannot.
15. Real use cases for stored procedures
Short answer: Most real procedures in Snowflake projects do loading, merging, cleanup, auditing or controlled admin work, usually triggered by a task.
These are the jobs you are most likely to build in a Hyderabad, Bengaluru, Pune or Chennai data team, whether at an IT services firm or a GCC:
If you work with dbt, you will also see procedures used alongside dbt models for steps that do not fit a model, such as admin or cleanup work. Many job titles now pair Snowflake with dbt: Naukri showed about 1,729 results for "dbt snowflake" on 25 October 2026 (keyword matches, not a count of open jobs).
16. Best practices for Snowflake stored procedures
Short answer: Keep procedures small and single-purpose, choose the rights model on purpose, handle and log errors, use binds or validated inputs for dynamic SQL, and keep the code in Git.
A checklist you can use on every procedure:
- One job per procedure. A procedure that loads, merges and purges is hard to test. Split it and call the parts in order.
- Decide rights deliberately. Owner's rights is the default, so write EXECUTE AS CALLER when you mean it, and add a comment explaining why.
- Test right after you create it. Snowflake does not fully validate code at creation, so run a CALL with sample inputs immediately.
- Handle errors in every production procedure. Use an EXCEPTION block or try/catch, and log the error to a table.
- Plan for partial failure. Procedures are not atomic, so design steps that can be re-run safely, for example with MERGE rather than plain INSERT.
- Avoid unsafe dynamic SQL. Use binds in JavaScript and validate any input you join into SQL strings, especially in owner's rights procedures.
- Use CREATE OR REPLACE with COPY GRANTS, or CREATE OR ALTER, so redeploying does not silently remove privileges.
- Include argument types in GRANT, ALTER and DROP. For example, GRANT USAGE ON PROCEDURE log_run(VARCHAR) TO ROLE etl_role.
- Grant to roles, not users. Follow RBAC and keep a small set of roles with USAGE on each procedure.
- Prefer Snowflake Scripting for SQL-heavy logic and Python for DataFrame or library logic; keep JavaScript for existing code.
- Keep the code in Git and deploy through scripts or Snowflake CLI rather than editing in production worksheets.
- Watch compute. A procedure scheduled every few minutes on a large warehouse can burn credits; size the warehouse for the job and let it auto-suspend.
- Stay under the 1 MB definition limit. If a procedure is that large, it should be several procedures or staged Python code.
17. Common errors and how to fix them
Short answer: Most stored procedure errors come from argument references, missing argument types, privileges, rights model surprises and code that was never tested after creation.
For debugging, run a test CALL right after creating it, return intermediate values while you test, write progress rows to a log table, and read the error message with SQLCODE and SQLERRM. Then remove the extra output once the procedure is stable.
18. Stored procedure interview questions to prepare
Short answer: Interviewers usually test the language options, the CALL syntax, owner's vs caller's rights, error handling, tasks and the procedure vs UDF difference.
Practise answering these out loud in two or three sentences each:
- Which languages can you use for a Snowflake stored procedure, and which must be in-line?
- What is the difference between owner's rights and caller's rights?
- When would you choose a UDF instead of a stored procedure?
- How do you return a table from a procedure?
- What happens if statement four of six fails inside a procedure?
- How would you migrate an Oracle PL/SQL procedure to Snowflake?
For the full concept bank, see our Snowflake interview questions. For realistic project situations, such as a failed nightly MERGE, see Snowflake scenario-based interview questions.
19. How Brolly Academy helps
Short answer: Brolly Academy's Snowflake course in Hyderabad covers tasks, streams and calling procedures from tasks, plus error handling and validation, inside a 45-day programme with projects and interview preparation.
If you want to learn procedures as part of a full Snowflake skill set, the Snowflake Training in Hyderabad course page (checked 25 October 2026) lists:
- Duration and modes: 45 days, in classroom or live online batches, plus a self-paced video plan
- Fees: Video ₹8,999, Online ₹17,999, Classroom ₹22,999, with EMI available
- Trainer: Mr. Bala Krishna, with 13+ years in data warehousing, SQL and Snowflake
- Syllabus: 15 modules, including a scheduling module on tasks, streams and calling procedures from tasks, and an advanced module covering error handling and validation, resource monitoring and best practices
- Projects: 6 projects, including ETL pipeline development and a financial data warehouse
- Certificate: a Brolly Academy completion certificate; SnowPro exams are run by Snowflake and their fees are separate
- Support: a free demo class, resume help and mock interviews
The course teaches Python integration through the Snowflake Python connector. Snowpark is not a syllabus module, so learn Python procedures through Snowflake's own documentation and our Snowpark guide.
Write your first Snowflake stored procedure with a trainer beside you
Reading syntax is one thing; debugging a failed MERGE at 2 a.m. is another. Brolly Academy's 45-day Snowflake course in Hyderabad, available as classroom, live online or video, walks you through stages, loading, streams, tasks and calling procedures from tasks, with 6 projects and mock interviews. Book a free demo class to see how it fits your schedule.
Frequently asked questions
What is a stored procedure in Snowflake?
A stored procedure in Snowflake is a named object that runs a sequence of steps, such as several SQL statements with loops, conditions and error handling, whenever you call it with CALL. You create it once with CREATE PROCEDURE and reuse it from worksheets, scripts, tasks or other procedures. Teams use procedures mainly for loading, merging, purging, auditing and controlled admin jobs that need more than one SQL statement.
Which languages can I use to write Snowflake stored procedures?
You can write the handler in Snowflake Scripting, which is SQL, or in JavaScript, Python, Java or Scala. Snowflake Scripting and JavaScript handlers must be written in-line inside the CREATE PROCEDURE statement. Python, Java and Scala handlers can be in-line or staged as files. Most SQL developers start with Snowflake Scripting, while data engineers who work with DataFrames often choose Python with Snowpark.
How do I create a stored procedure in Snowflake?
Use CREATE OR REPLACE PROCEDURE with a name, typed arguments, a RETURNS clause, a LANGUAGE clause and the code between double dollar signs. For Snowflake Scripting, write LANGUAGE SQL and put your statements inside BEGIN and END. Then run a test CALL straight away, because Snowflake does not fully validate the code when you create it, so some mistakes only appear on the first call.
How do I call a stored procedure in Snowflake?
Run CALL followed by the procedure name and its arguments, for example CALL log_run('daily_sales_load'). You can also call a procedure from another procedure, capture its result in a RESULTSET variable, or call it from a task on a schedule. Only one stored procedure can be called per SQL statement, but a procedure can call other procedures and even itself recursively.
What is Snowflake Scripting?
Snowflake Scripting is Snowflake's procedural extension to SQL. It adds variables, DECLARE, BEGIN and EXCEPTION blocks, IF and CASE logic, loops, cursors, RESULTSETs and named exceptions. You use it by writing LANGUAGE SQL in a stored procedure, or by running an anonymous block. In Snowflake CLI, the Classic Console or the Python connector, you wrap anonymous blocks in EXECUTE IMMEDIATE with double dollar signs.
Why do I need a colon before arguments in Snowflake Scripting?
Inside a SQL statement, such as an INSERT or a SELECT in a RESULTSET, Snowflake Scripting needs a colon before a variable or argument name, like :job_name, so it knows you mean the variable and not a column. Outside SQL statements, in assignments, conditions and RETURN, you use the plain name. When you build a string for EXECUTE IMMEDIATE, you also join the plain name without a colon.
Can a Snowflake stored procedure return a table?
Yes. Write RETURNS TABLE with the column names and types, put a query into a RESULTSET variable, and return it with RETURN TABLE(res). Tabular procedures work with Snowflake Scripting and Python, and with Java and Scala, where tabular procedures are in preview. JavaScript procedures cannot return a table and return only a single value, so they use a VARIANT or a delimited string for richer output.
What is the difference between a stored procedure and a UDF in Snowflake?
A stored procedure does work: it runs DDL, DML and multi-step jobs, and you run it with a standalone CALL. A UDF computes and returns a value, and you use it inside a SQL statement such as SELECT. A procedure's return value is optional, while a UDF must return a value. You can use many UDFs in one statement, but only one procedure per CALL.
When should I use a UDF instead of a stored procedure?
Use a UDF when you need a value for every row or group inside a query, such as a tax calculation, a cleaned-up code or a category label used in SELECT, WHERE or JOIN. UDFs only run simple queries and cannot run DDL or DML. If your logic needs to create, update, delete or purge data, or run several steps in order, a stored procedure is the right choice.
What is EXECUTE AS CALLER vs EXECUTE AS OWNER in Snowflake?
EXECUTE AS OWNER, the default, runs the procedure with the privileges of the role that owns it, hides the source from non-owners and cannot change session state. EXECUTE AS CALLER runs with the caller's privileges, context and session variables. Owner's rights suits controlled admin jobs that users run without direct table access, while caller's rights suits helper code that should respect each user's own access.
Can I change a procedure from owner's rights to caller's rights?
Yes. You can switch an existing procedure with ALTER PROCEDURE, including the argument types in the name, followed by EXECUTE AS CALLER or EXECUTE AS OWNER. You can also recreate it with CREATE OR REPLACE and the new clause. Remember that any procedure called from inside an owner's rights procedure also runs with owner's rights, so test nested calls after you change the setting.
How do I handle errors in a Snowflake stored procedure?
In Snowflake Scripting, add an EXCEPTION block with handlers such as WHEN STATEMENT_ERROR, WHEN EXPRESSION_ERROR and WHEN OTHER, and read SQLCODE, SQLERRM and SQLSTATE to describe the error. You can declare named exceptions with a number and message and throw them with RAISE. In JavaScript use try and catch, and in Python catch the exceptions you expect and log a clear message.
What do SQLCODE, SQLERRM and SQLSTATE mean?
They are built-in variables you can read inside a Snowflake Scripting exception handler. SQLCODE is a 5-digit signed integer error code, SQLERRM is the error message text, and SQLSTATE is a 5-character error state code. Many teams insert all three, along with the job name and time, into an error log table so support engineers can see exactly what failed.
Are Snowflake stored procedures transactional?
No, not automatically. Snowflake's CREATE PROCEDURE reference says procedures are not atomic, so if one statement fails, the statements before it are not necessarily rolled back. Design your procedures so they can be re-run safely, for example by using MERGE instead of plain INSERT, and add error handling that logs what happened so you can fix the data and run the job again.
How do I schedule a stored procedure in Snowflake?
Create a task whose body is a CALL statement, give it a schedule such as '60 MINUTES' or a cron expression with a time zone, and name a warehouse or make it serverless. A new task starts suspended, so run ALTER TASK with RESUME to start it, or EXECUTE TASK for a single run. A task can also run only when a stream has new data.
Can a task call a Python or JavaScript stored procedure?
Yes. Snowflake's tasks documentation says tasks can call stored procedures written in JavaScript, Python, Java, Scala or Snowflake Scripting. The task body is simply a CALL statement. You can also chain several tasks into a task graph so that procedures run in series or in parallel, which is how many teams build nightly load pipelines with streams, tasks and procedures.
How do I write a stored procedure in Python?
Write LANGUAGE PYTHON, choose a RUNTIME_VERSION such as 3.11, add the snowflake-snowpark-python package in PACKAGES, and set HANDLER to your function name. The function's first argument must be the Snowpark Session, which Snowflake creates and passes in when you call the procedure. Python 3.10 to 3.14 are generally available for procedures, and Python 3.9 is deprecated.
Should I use Snowflake Scripting or JavaScript for new procedures?
For SQL-heavy logic, Snowflake Scripting is usually easier to read and maintain, and it can return a table with RETURNS TABLE, which JavaScript cannot. JavaScript is still fully supported and is common in older Snowflake projects, so you should be able to read and fix it. Choose Python when you need DataFrames or Python libraries, and keep Java or Scala for teams already using them.
Why does my JavaScript procedure say an argument is undefined?
JavaScript is case-sensitive, while SQL is not. An argument declared in lowercase in the CREATE PROCEDURE statement must be written in uppercase inside the JavaScript code, so status becomes STATUS. Using the lowercase name is the most common JavaScript procedure bug. You can also double-quote the argument name in SQL if you want to keep a specific case.
Why does DROP PROCEDURE say the procedure does not exist?
Snowflake identifies a procedure by its name plus its argument types, because you can overload procedures with different signatures. So DROP, ALTER and GRANT statements must include the argument types, for example DROP PROCEDURE log_run(VARCHAR). If you leave the types out, or your role lacks the right privileges on the procedure, Snowflake reports that the procedure does not exist or is not authorised.
Can I use a stored procedure inside a SELECT statement?
No, not directly. A stored procedure runs as a standalone CALL, and its return value cannot be used in an expression the way a UDF can. Snowflake lists workarounds: capture the value in a Snowflake Scripting variable, call a table-returning procedure in a FROM clause, read the result with RESULT_SCAN, or store results in a table. If you need a value per row, write a UDF.
What are the limitations of Snowflake stored procedures?
The main limits are: only one procedure per CALL statement, the return value cannot be used directly in expressions, the definition is limited to 1 MB, procedures are not atomic, and JavaScript procedures return only one value. Snowflake also does not fully validate code at creation, so errors may only appear on the first call. Owner's rights procedures also cannot change session state.
How do I debug a Snowflake stored procedure?
Run a test CALL right after creating it, because some errors only appear at run time. While testing, return intermediate values, write progress rows to a log table and catch errors with an EXCEPTION block that returns SQLCODE and SQLERRM. In JavaScript, return the error code, state, message and stack trace from the catch block. Remove the extra output once the procedure is stable.
Are stored procedures asked in Snowflake interviews and job descriptions?
Yes. Of 14 Snowflake job descriptions we read on Naukri and LinkedIn on 25 October 2026, roles at TCS, DTCC and Capgemini named stored procedures or UDFs, and an EPAM Systems role asked for Snowflake Scripting and Snowpark for Python. Interviewers commonly ask about languages, owner's vs caller's rights, error handling, scheduling with tasks and the difference between procedures and UDFs.
Does Brolly Academy's Snowflake course cover stored procedures?
Brolly Academy's 45-day Snowflake course in Hyderabad includes a scheduling module on tasks, streams and calling procedures from tasks, and an advanced module covering error handling and validation. Fees are ₹8,999 for video, ₹17,999 for online and ₹22,999 for classroom, with EMI available. Snowpark is not a syllabus module, so learn Python procedures from Snowflake's documentation alongside the course.
Learn with Brolly Academy
Review the syllabus, practical projects and batch timings, and book a free demo before you enrol.
Related Articles
Continue Your Learning
Snowflake Training in Hyderabad
45-day course covering Snowflake architecture, warehouses, data loading, security, performance and real-time projects, in classroom, live online and recorded plans.
View Coursedbt Course
Learn dbt, the transformation tool most Snowflake job descriptions ask for, to build tested, documented data models.
View CourseDatabricks Course
Add the lakehouse side of modern data engineering with Databricks, Spark and Delta Lake, often used alongside Snowflake.
View Course