Stored procedures and query parameters in ClickHouse

### Parameter syntax[​](#parameter-syntax "Direct link to Parameter syntax")

Parameters are referenced using: `{parameter_name: DataType}`

- `parameter_name` - The name of the parameter (without the `param_` prefix)
- `DataType` - The ClickHouse data type to cast the parameter to

### Data type examples[​](#data-type-examples "Direct link to Data type examples")

Tables and sample data for example

```
-- 1. Create a table for string and number tests
CREATE TABLE IF NOT EXISTS users (
    name String,
    age UInt8,
    salary Float64
) ENGINE = Memory;

INSERT INTO users VALUES
    ('John Doe', 25, 75000.50),
    ('Jane Smith', 30, 85000.75),
    ('Peter Jones', 20, 50000.00);

-- 2. Create a table for date and timestamp tests
CREATE TABLE IF NOT EXISTS events (
    event_date Date,
    event_timestamp DateTime
) ENGINE = Memory;

INSERT INTO events VALUES
    ('2024-01-15', '2024-01-15 14:30:00'),
    ('2024-01-15', '2024-01-15 15:00:00'),
    ('2024-01-16', '2024-01-16 10:00:00');

-- 3. Create a table for array tests
CREATE TABLE IF NOT EXISTS products (
    id UInt32,
    name String
) ENGINE = Memory;

INSERT INTO products VALUES (1, 'Laptop'), (2, 'Monitor'), (3, 'Mouse'), (4, 'Keyboard');

-- 4. Create a table for Map (struct-like) tests
CREATE TABLE IF NOT EXISTS accounts (
    user_id UInt32,
    status String,
    type String
) ENGINE = Memory;

INSERT INTO accounts VALUES
    (101, 'active', 'premium'),
    (102, 'inactive', 'basic'),
    (103, 'active', 'basic');

-- 5. Create a table for Identifier tests
CREATE TABLE IF NOT EXISTS sales_2024 (
    value UInt32
) ENGINE = Memory;

INSERT INTO sales_2024 VALUES (100), (200), (300);
```

- Strings & Numbers- Dates & Times- Arrays- Maps- Identifiers

```
SET param_name = 'John Doe';
SET param_age = 25;
SET param_salary = 75000.50;

SELECT name, age, salary FROM users
WHERE name = {name: String}
  AND age >= {age: UInt8}
  AND salary <= {salary: Float64};
```

```
SET param_date = '2024-01-15';
SET param_timestamp = '2024-01-15 14:30:00';

SELECT * FROM events
WHERE event_date = {date: Date}
   OR event_timestamp > {timestamp: DateTime};
```

```
SET param_ids = [1, 2, 3, 4, 5];

SELECT * FROM products WHERE id IN {ids: Array(UInt32)};
```

```
SET param_filters = {'target_status': 'active'};

SELECT user_id, status, type FROM accounts
WHERE status = arrayElement(
    mapValues({filters: Map(String, String)}),
    indexOf(mapKeys({filters: Map(String, String)}), 'target_status')
);
```

```
SET param_table = 'sales_2024';

SELECT count() FROM {table: Identifier};
```

  

For use of query parameters in [language clients](/docs/integrations/language-clients), refer to the documentation for
the specific language client you're interested in.

### Limitations of query parameters[​](#limitations-of-query-parameters "Direct link to Limitations of query parameters")

Query parameters are **not general text substitutions**. They have specific limitations:

1. They're **primarily intended for SELECT statements** - the best support is in SELECT queries
2. They **work as identifiers or literals** - they can't substitute arbitrary SQL fragments
3. They have **limited DDL support** - they're supported in `CREATE TABLE`, but not in `ALTER TABLE`

**What WORKS:**

```
-- ✓ Values in WHERE clause
SELECT * FROM users WHERE id = {user_id: UInt64};

-- ✓ Table/database names
SELECT * FROM {db: Identifier}.{table: Identifier};

-- ✓ Values in IN clause
SELECT * FROM products WHERE id IN {ids: Array(UInt32)};

-- ✓ CREATE TABLE
CREATE TABLE {table_name: Identifier} (id UInt64, name String) ENGINE = MergeTree() ORDER BY id;
```

**What DOESN'T work:**

```
-- ✗ Column names in SELECT (use Identifier carefully)
SELECT {column: Identifier} FROM users;  -- Limited support

-- ✗ Arbitrary SQL fragments
SELECT * FROM users {where_clause: String};  -- NOT SUPPORTED

-- ✗ ALTER TABLE statements
ALTER TABLE {table: Identifier} ADD COLUMN new_col String;  -- NOT SUPPORTED

-- ✗ Multiple statements
{statements: String};  -- NOT SUPPORTED
```

### Security best practices[​](#security-best-practices "Direct link to Security best practices")

**Always use query parameters for user input:**

```
# ✓ SAFE - Uses parameters
user_input = request.get('user_id')
result = client.query(
    "SELECT * FROM orders WHERE user_id = {uid: UInt64}",
    parameters={'uid': user_input}
)

# ✗ DANGEROUS - SQL injection risk!
user_input = request.get('user_id')
result = client.query(f"SELECT * FROM orders WHERE user_id = {user_input}")
```

**Validate input types:**

```
def get_user_orders(user_id: int, start_date: str):
    # Validate types before querying
    if not isinstance(user_id, int) or user_id <= 0:
        raise ValueError("Invalid user_id")

    # Parameters enforce type safety
    return client.query(
        """
        SELECT * FROM orders
        WHERE user_id = {uid: UInt64}
            AND order_date >= {start: Date}
        """,
        parameters={'uid': user_id, 'start': start_date}
    )
```

### MySQL protocol prepared statements[​](#mysql-protocol-prepared-statements "Direct link to MySQL protocol prepared statements")

ClickHouse's [MySQL interface](/docs/interfaces/mysql) includes minimal support for prepared statements (`COM_STMT_PREPARE`, `COM_STMT_EXECUTE`, `COM_STMT_CLOSE`), primarily to enable connectivity with tools like Tableau Online that wrap queries in prepared statements.

**Key limitations:**

- **Parameter binding isn't supported** - You can't use `?` placeholders with bound parameters
- Queries are stored but not parsed during `PREPARE`
- Implementation is minimal and designed for specific BI tool compatibility

**Example of what doesn't work:**

```
-- This MySQL-style prepared statement with parameters does NOT work in ClickHouse
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;  -- Parameter binding not supported
```

Tip

**Use ClickHouse's native query parameters instead.** They provide full parameter binding support, type safety, and SQL injection prevention across all ClickHouse interfaces:

```
-- ClickHouse native query parameters (recommended)
SET param_user_id = 12345;
SELECT * FROM users WHERE id = {user_id: UInt64};
```

For more details, see the [MySQL Interface documentation](/docs/interfaces/mysql) and the [blog post on MySQL support](https://clickhouse.com/blog/mysql-support-in-clickhouse-the-journey).

## Summary[​](#summary "Direct link to Summary")

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

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Traditional Stored Procedure Pattern ClickHouse Alternative|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Simple calculations and transformations User-Defined Functions (UDFs)|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Reusable parameterized queries Parameterized Views|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Pre-computed aggregations Materialized Views|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Scheduled batch processing Refreshable Materialized Views|  |  |  |  | | --- | --- | --- | --- | | Complex multi-step ETL Chained materialized views or external orchestration (Python, Airflow, dbt)|  |  | | --- | --- | | Business logic with control flow Application code | | | | | | | | | | | | | |

### Use of query parameters[​](#summary-query-parameters "Direct link to Use of query parameters")

Query parameters can be used for:

- Preventing SQL injection
- Parameterized queries with type safety
- Dynamic filtering in applications
- Reusable query templates

## Related documentation[​](#related-documentation "Direct link to Related documentation")