How to Use Stored Procedures in ClickHouse

What are stored procedures in ClickHouse?

Stored procedures are named SQL blocks saved in the ClickHouse catalog. They let you bundle multiple statements—such as inserts, updates, and selects—so clients call one object instead of sending many separate requests.

Why use stored procedures instead of client-side scripts?

They reduce network round-trips, centralize business logic, and allow fine-grained permissioning with GRANT EXECUTE. In an ecommerce stack, a single procedure can insert an order, update product stock, and return the new order ID.

How do I create a stored procedure?

Use CREATE PROCEDURE with an optional parameter list and BEGIN … END block. Include multiple SQL commands separated by semicolons.

CREATE PROCEDURE create_order(cust_id UInt64, prod_id UInt64, qty UInt8)
BEGIN
INSERT INTO Orders (customer_id, order_date, total_amount)
VALUES (cust_id, now(), 0);

INSERT INTO OrderItems (order_id, product_id, quantity)
VALUES (last_insert_id(), prod_id, qty);

SELECT last_insert_id() AS new_order_id;
END

How do I run a stored procedure?

Invoke CALL. Supply arguments in order:

CALL create_order(42, 17, 3);

Can I alter or drop procedures?

Yes. Use ALTER PROCEDURE ... AS to replace the body, or DROP PROCEDURE to remove it. Always test changes in staging first.

Best practices for procedure design?

Keep procedures single-purpose, validate inputs early, wrap mutating logic in a transaction when experimental transactions are enabled, and add COMMENT ON for discoverability.

Common ecommerce use cases

• Create orders and adjust inventory atomically.
• Send batched promotional emails by looping through Customers.
• Generate daily sales summaries into aggregate tables.

Example: update stock after purchase

CREATE PROCEDURE adjust_stock(p_id UInt64, p_qty UInt8)
BEGIN
ALTER TABLE Products UPDATE stock = stock - p_qty WHERE id = p_id;
END;

Security considerations

Grant EXECUTE on procedures to specific roles, not the underlying tables. This limits users to vetted logic paths.

How to list existing procedures?

Query system.procedures:

SELECT name, create_query FROM system.procedures;

Debugging tips

Use FORMAT JSONEachRow inside SELECT statements for interim output, log business events to audit tables, and version procedures via naming conventions like v1, v2.