Stored procedures and query parameters in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/developer/stored-procedures-and-prepared-statements.md)

If you're coming from a traditional relational database, you may be looking for stored procedures and prepared statements in ClickHouse.
This guide explains ClickHouse's approach to these concepts and provides recommended alternatives.

## Alternatives to stored procedures in ClickHouse[​](#alternatives-to-stored-procedures "Direct link to Alternatives to stored procedures in ClickHouse")

ClickHouse doesn't support traditional stored procedures with control flow logic (`IF`/`ELSE`, loops, etc.).
This is an intentional design decision based on ClickHouse's architecture as an analytical database.
Loops are discouraged for analytical databases because processing O(n) simple queries is usually slower than processing fewer complex queries.

ClickHouse is optimized for:

- **Analytical workloads** - Complex aggregations over large datasets
- **Batch processing** - Handling large data volumes efficiently
- **Declarative queries** - SQL queries that describe what data to retrieve, not how to process it

Stored procedures with procedural logic work against these optimizations. Instead, ClickHouse provides alternatives that align with its strengths.

### User-Defined Functions (UDFs)[​](#user-defined-functions "Direct link to User-Defined Functions (UDFs)")

User-Defined Functions let you encapsulate reusable logic without control flow. ClickHouse supports two types:

#### Lambda-based UDFs[​](#lambda-based-udfs "Direct link to Lambda-based UDFs")

Create functions using SQL expressions and lambda syntax:

Sample data for examples

```
-- Create the products table
CREATE TABLE products (
    product_id UInt32,
    product_name String,
    price Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY product_id;

-- Insert sample data
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 899.99),
(2, 'Wireless Mouse', 24.99),
(3, 'USB-C Cable', 12.50),
(4, 'Monitor', 299.00),
(5, 'Keyboard', 79.99),
(6, 'Webcam', 54.95),
(7, 'Desk Lamp', 34.99),
(8, 'External Hard Drive', 119.99),
(9, 'Headphones', 149.00),
(10, 'Phone Stand', 15.99);
```

```
-- Simple calculation function
CREATE FUNCTION calculate_tax AS (price, rate) -> price * rate;

SELECT
    product_name,
    price,
    calculate_tax(price, 0.08) AS tax
FROM products;
```

```
-- Conditional logic using if()
CREATE FUNCTION price_tier AS (price) ->
    if(price < 100, 'Budget',
       if(price < 500, 'Mid-range', 'Premium'));

SELECT
    product_name,
    price,
    price_tier(price) AS tier
FROM products;
```

```
-- String manipulation
CREATE FUNCTION format_phone AS (phone) ->
    concat('(', substring(phone, 1, 3), ') ',
           substring(phone, 4, 3), '-',
           substring(phone, 7, 4));

SELECT format_phone('5551234567');
-- Result: (555) 123-4567
```

**Limitations:**

- No loops or complex control flow
- Can't modify data (`INSERT`/`UPDATE`/`DELETE`)
- Recursive functions not allowed

See [`CREATE FUNCTION`](/docs/sql-reference/statements/create/function) for complete syntax.

#### Executable UDFs[​](#executable-udfs "Direct link to Executable UDFs")

For more complex logic, use executable UDFs that call external programs:

```
<!-- /etc/clickhouse-server/sentiment_analysis_function.xml -->
<functions>
    <function>
        <type>executable</type>
        <name>sentiment_score</name>
        <return_type>Float32</return_type>
        <argument>
            <type>String</type>
        </argument>
        <format>TabSeparated</format>
        <command>python3 /opt/scripts/sentiment.py</command>
    </function>
</functions>
```

```
-- Use the executable UDF
SELECT
    review_text,
    sentiment_score(review_text) AS score
FROM customer_reviews;
```

Executable UDFs can implement arbitrary logic in any language (Python, Node.js, Go, etc.).

See [Executable UDFs](/docs/sql-reference/functions/udf) for details.

### Parameterized views[​](#parameterized-views "Direct link to Parameterized views")

Parameterized views act like functions that return datasets.
They're ideal for reusable queries with dynamic filtering:

Sample data for example

```
-- Create the sales table
CREATE TABLE sales (
  date Date,
  product_id UInt32,
  product_name String,
  category String,
  quantity UInt32,
  revenue Decimal(10, 2),
  sales_amount Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (date, product_id);

-- Insert sample data
INSERT INTO sales VALUES
('2024-01-05', 12345, 'Laptop Pro', 'Electronics', 2, 1799.98, 1799.98),
('2024-01-06', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-01-10', 12346, 'Wireless Mouse', 'Electronics', 5, 124.95, 124.95),
('2024-01-15', 12347, 'USB-C Cable', 'Accessories', 10, 125.00, 125.00),
('2024-01-20', 12345, 'Laptop Pro', 'Electronics', 3, 2699.97, 2699.97),
('2024-01-25', 12348, 'Monitor 4K', 'Electronics', 2, 598.00, 598.00),
('2024-02-01', 12345, 'Laptop Pro', 'Electronics', 1, 899.99, 899.99),
('2024-02-05', 12349, 'Keyboard Mechanical', 'Accessories', 4, 319.96, 319.96),
('2024-02-10', 12346, 'Wireless Mouse', 'Electronics', 8, 199.92, 199.92),
('2024-02-15', 12350, 'Webcam HD', 'Electronics', 3, 164.85, 164.85);
```

```
-- Create a parameterized view
CREATE VIEW sales_by_date AS
SELECT
    date,
    product_id,
    sum(quantity) AS total_quantity,
    sum(revenue) AS total_revenue
FROM sales
WHERE date BETWEEN {start_date:Date} AND {end_date:Date}
GROUP BY date, product_id;
```

```
-- Query the view with parameters
SELECT *
FROM sales_by_date(start_date='2024-01-01', end_date='2024-01-31')
WHERE product_id = 12345;
```

#### Common use cases[​](#common-use-cases "Direct link to Common use cases")

- Dynamic date range filtering
- User-specific data slicing
- [Multi-tenant data access](/docs/cloud/bestpractices/multi-tenancy)
- Report templates
- [Data masking](/docs/cloud/guides/data-masking)

```
-- More complex parameterized view
CREATE VIEW top_products_by_category AS
SELECT
    category,
    product_name,
    revenue,
    rank
FROM (
    SELECT
        category,
        product_name,
        revenue,
        rank() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
    FROM (
        SELECT
            category,
            product_name,
            sum(sales_amount) AS revenue
        FROM sales
        WHERE category = {category:String}
            AND date >= {min_date:Date}
        GROUP BY category, product_name
    )
)
WHERE rank <= {top_n:UInt32};

-- Use it
SELECT * FROM top_products_by_category(
    category='Electronics',
    min_date='2024-01-01',
    top_n=10
);
```

See the [Parameterized Views](/docs/sql-reference/statements/create/view#parameterized-view) section for more information.

### Materialized views[​](#materialized-views "Direct link to Materialized views")

Materialized views are ideal for pre-computing expensive aggregations that would traditionally be done in stored procedures. If you're coming from a traditional database, think of a materialized view as an **INSERT trigger** that automatically transforms and aggregates data as it's inserted into the source table:

```
-- Source table
CREATE TABLE page_views (
    user_id UInt64,
    page String,
    timestamp DateTime,
    session_id String
)
ENGINE = MergeTree()
ORDER BY (user_id, timestamp);

-- Materialized view that maintains aggregated statistics
CREATE MATERIALIZED VIEW daily_user_stats
ENGINE = SummingMergeTree()
ORDER BY (date, user_id)
AS SELECT
    toDate(timestamp) AS date,
    user_id,
    count() AS page_views,
    uniq(session_id) AS sessions,
    uniq(page) AS unique_pages
FROM page_views
GROUP BY date, user_id;