# Stored Procedures & Functions

Call stored procedures and user-defined functions across all supported databases with type-safe parameter handling.

## Overview

Prax provides a unified API for calling stored procedures and functions across PostgreSQL, MySQL, MSSQL, and SQLite. Support includes IN/OUT/INOUT parameters, table-valued functions, and custom aggregates.

Feature | PostgreSQL | MySQL | SQLite | MSSQL | MongoDB
--- | --- | --- | --- | --- | ---
Stored Procedures | ✅ | ✅ | ❌ | ✅ | ❌
User-Defined Functions | ✅ | ✅ | ✅ Rust | ✅ | ✅ $function
Table-Valued Functions | ✅ | ❌ | ❌ | ✅ | ❌
Aggregate Functions | ✅ | ✅ | ✅ Rust | ✅ | ✅ $accumulator

## Calling Procedures

Use `ProcedureCall` to invoke stored procedures with type-safe parameters.

## OUT and INOUT Parameters

Handle output parameters with `ParameterMode`.

## User-Defined Functions

Call scalar and table-valued functions with the same fluent API.

## SQLite Rust UDFs

Since SQLite doesn't support stored procedures, register Rust functions directly with the connection.

Note: SQLite UDFs are registered per-connection. Use the connection pool's `after_connect` hook to register functions on all connections.

## MongoDB $function & $accumulator

Define custom JavaScript functions for MongoDB aggregation pipelines.

## Procedure Migrations

Version control your stored procedures with Prax migrations. The differ detects changes automatically.

## Generated SQL by Database

Prax generates the correct syntax for each database:

## Best Practices

#### Use Transactions

Wrap procedure calls in transactions when they modify data. Some procedures handle their own transactions—check the procedure definition.

#### Handle Multiple Result Sets

Some procedures return multiple result sets. Use `result.next_result_set()` to iterate through them.

#### Version Control Procedures

Keep procedure definitions in migrations. Use `ProcedureDiffer` to detect changes and generate appropriate ALTER/DROP/CREATE statements.

#### Avoid N+1 with Procedures

Don't call procedures in a loop. Batch operations when possible, or design procedures to accept arrays/table-valued parameters.