Class: StoredProcedureBuilder

Defined in: src/procedure/StoredProcedureBuilder.ts:257

Fluent builder for configuring and executing database stored procedures and routines across all supported database engines.

Supports input/output/inout parameters, automatic SQL data type inference, multiple tabular result sets (GridReader), execution timeouts, transaction binding, and error translation.

Multi-Database Compatibility

• Microsoft SQL Server (MSSQL): Full support for EXEC, @Parameters, OUTPUT params, RETURN codes, and multiple SELECT tables.
• MySQL / MariaDB: Full support for CALL procedure_name(?, ?), INOUT and OUT parameters, and multiple result sets.
• PostgreSQL: Support for CALL sp_name($1, $2) procedures and SELECT * FROM fn_name($1, $2) functions.
• Oracle Database: Full support for BEGIN procedure_name(:p1, :p2); END; and PL/SQL cursors.
• SQLite / LibSQL / Neon / Turso: Supported via parameterized queries or emulated procedure handlers.

Constructors

Constructor

new StoredProcedureBuilder(adapter, procedureName): StoredProcedureBuilder

Defined in: src/procedure/StoredProcedureBuilder.ts:268

Initializes a new instance of the StoredProcedureBuilder.

Parameters

Parameter	Type	Description
adapter	IDbAdapter	The active database adapter.
procedureName	string	Name of the stored procedure or function in the database.

Returns

StoredProcedureBuilder

Methods

getName()

getName(): string

Defined in: src/procedure/StoredProcedureBuilder.ts:282

Returns the configured name of the stored procedure.

Returns

string

Usecase

Useful for logging or diagnostic messages.

---

getParams()

getParams(): AdapterParam[]

Defined in: src/procedure/StoredProcedureBuilder.ts:291

Returns an array of configured adapter parameters.

Returns

AdapterParam[]

Usecase

Useful for inspecting bound parameters before execution.

---

input()

input(params): this

Defined in: src/procedure/StoredProcedureBuilder.ts:334

Sets all input parameters at once using a plain key-value object. SQL data types are inferred automatically from JavaScript runtime values:

• string → NVarChar
• number → Int (whole) or Decimal (fractional)
• boolean → Bit
• Date → DateTime2
• bigint → BigInt

Parameters

Parameter	Type	Description
params	Record<string, unknown>	Object containing parameter names and values.

Returns

this

this builder instance for chaining.

Usecase

Fast, clean parameter definition without manual SQL type specification.

Example

SQL Server (MSSQL):

await context.procedure('usp_GetCustomerOrders')
  .input({ CustomerId: 42, Status: 'SHIPPED', MinTotal: 50.00 })
  .query<Order>();

MySQL:

await context.procedure('sp_get_customer_orders')
  .input({ p_customer_id: 42, p_status: 'SHIPPED' })
  .query<Order>();

PostgreSQL:

await context.procedure('fn_get_customer_orders')
  .input({ p_customer_id: 42, p_status: 'SHIPPED' })
  .query<Order>();

---

output()

output<TOut>(paramNames?): SprocOutputBuilder<TOut>

Defined in: src/procedure/StoredProcedureBuilder.ts:380

Declares typed output parameters by name and returns an SprocOutputBuilder.

Type Parameters

Type Parameter	Default type
TOut extends object	Record<string, unknown>

Parameters

Parameter	Type	Description
paramNames?	keyof TOut[]	Optional array of parameter names.

Returns

SprocOutputBuilder<TOut>

An SprocOutputBuilder<TOut> for executing the query.

Usecase

Specify procedure OUTPUT parameters with complete TypeScript type safety on the returned .out property.

Example

SQL Server (MSSQL):

const { out } = await context.procedure('usp_RegisterAccount')
  .input({ Email: 'dev@entityTS.org', PasswordHash: '...' })
  .output<{ AccountId: number; ActivationToken: string }>()
  .run();
console.log('Created ID:', out.AccountId);

MySQL:

const { out } = await context.procedure('sp_register_account')
  .input({ p_email: 'dev@entityTS.org', p_hash: '...' })
  .output<{ out_account_id: number; out_token: string }>()
  .run();

PostgreSQL:

const { out } = await context.procedure('sp_register_account')
  .input({ in_email: 'dev@entityTS.org', in_hash: '...' })
  .output<{ out_account_id: number }>()
  .run();

---

query()

query<T>(): Promise<T[]>

Defined in: src/procedure/StoredProcedureBuilder.ts:435

Executes the stored procedure and returns typed records directly as an array. Shorthand for .executeQuery<T>() when output parameters are not needed.

Type Parameters

Type Parameter	Default type
T	unknown

Returns

Promise<T[]>

A Promise resolving to an array of typed row objects.

Usecase

Fetch tabular records from a stored procedure in a single clean call.

Example

SQL Server (MSSQL):

const activeUsers = await context.procedure('usp_GetActiveUsers')
  .input({ MinimumPoints: 100 })
  .query<User>();

PostgreSQL:

const activeUsers = await context.procedure('fn_get_active_users')
  .input({ min_points: 100 })
  .query<User>();

MySQL:

const activeUsers = await context.procedure('sp_get_active_users')
  .input({ min_points: 100 })
  .query<User>();

---

queryMultiple()

queryMultiple<T>(): Promise<T>

Defined in: src/procedure/StoredProcedureBuilder.ts:461

Executes the stored procedure and returns multiple typed record sets (tables) directly as a tuple.

Type Parameters

Type Parameter	Default type
T extends unknown[]	unknown[]

Returns

Promise<T>

A Promise resolving to a tuple of typed arrays, e.g. [OrderHeader[], OrderItem[]].

Usecase

Ideal for stored procedures returning multiple tables in a single round-trip without output params.

Example

SQL Server (MSSQL):

const [customers, orders, stats] = await context.procedure('usp_GetDashboard')
  .input({ CustomerId: 101 })
  .queryMultiple<[Customer[], Order[], Stat[]]>();

MySQL:

const [customers, orders, stats] = await context.procedure('sp_get_dashboard')
  .input({ p_customer_id: 101 })
  .queryMultiple<[Customer[], Order[], Stat[]]>();

---

reader()

reader<TOut>(): Promise<MultipleResultsReader<TOut>>

Defined in: src/procedure/StoredProcedureBuilder.ts:483

Executes the stored procedure and returns a sequential MultipleResultsReader (similar to Dapper's GridReader). Allows reading result tables one-by-one with .read<T>().

Type Parameters

Type Parameter	Default type
TOut	Record<string, unknown>

Returns

Promise<MultipleResultsReader<TOut>>

Usecase

Useful when consuming multiple tables sequentially or when tables vary by branch logic.

Example

const reader = await context.procedure('usp_GetComplexReport')
  .input({ CompanyId: 10 })
  .reader();

const company = reader.readFirst<Company>(); // Table 1
const departments = reader.read<Department>(); // Table 2
const employees   = reader.read<Employee>();   // Table 3

---

scalar()

scalar<T>(): Promise<T>

Defined in: src/procedure/StoredProcedureBuilder.ts:507

Executes the stored procedure and returns a single scalar value from the first column of the first row.

Type Parameters

Type Parameter	Default type
T	unknown

Returns

Promise<T>

A Promise resolving to the scalar value.

Usecase

Quick execution for procedures returning counts, IDs, or single computed values.

Example

SQL Server / MySQL / PostgreSQL:

const totalRevenue = await context.procedure('usp_CalculateRevenue')
  .input({ Year: 2026, Month: 9 })
  .scalar<number>();

---

run()

run(): Promise<{ rowsAffected: number; returnValue: number; }>

Defined in: src/procedure/StoredProcedureBuilder.ts:539

Executes the stored procedure with no return set (fire-and-forget or DML mutation).

Returns

Promise<{ rowsAffected: number; returnValue: number; }>

Object containing rowsAffected and returnValue.

Usecase

Execute procedures performing maintenance, cleanup, partition rotations, or sending notifications.

Example

SQL Server (MSSQL):

const { rowsAffected, returnValue } = await context.procedure('usp_PurgeOldSessions')
  .input({ OlderThanDays: 30 })
  .run();

PostgreSQL:

await context.procedure('sp_purge_old_sessions')
  .input({ older_than_days: 30 })
  .run();

MySQL:

await context.procedure('sp_purge_old_sessions')
  .input({ older_than_days: 30 })
  .run();

---

withParam()

withParam(name, value, type?, options?): this

Defined in: src/procedure/StoredProcedureBuilder.ts:566

Adds a single input parameter with an explicit SQL type and optional size/precision constraints.

Parameters

Parameter	Type	Description
name	string	Parameter name (leading @ is automatically handled).
value	unknown	Parameter value.
type?	SqlType	Optional explicit SqlType.
options?	ParamOptions	Optional length, precision, or scale options.

Returns

this

this builder instance for chaining.

Usecase

Use this when fine-grained SQL type control (e.g. VarChar(50) vs NVarChar(MAX)) is required.

Example

MSSQL:

context.procedure('usp_SaveCustomer')
  .withParam('CustomerCode', 'CUST-100', SqlType.VarChar, { maxLength: 20 })
  .withParam('CreditLimit', 5000.50, SqlType.Decimal, { precision: 18, scale: 2 });

---

withParams()

withParams(params): this

Defined in: src/procedure/StoredProcedureBuilder.ts:583

Adds multiple input parameters from a key-value object with auto-inferred types.

Parameters

Parameter	Type
params	Record<string, unknown>

Returns

this

Deprecated

Prefer .input({ ... }) for simplicity.

---

withOutputParam()

withOutputParam(name, type?, options?): this

Defined in: src/procedure/StoredProcedureBuilder.ts:602

Adds an output parameter with an explicit SQL type and sizing options.

Parameters

Parameter	Type	Default value	Description
name	string	undefined	Parameter name.
type	SqlType	SqlType.VarChar	Explicit SQL type (defaults to SqlType.VarChar).
options?	ParamOptions	undefined	Optional length, precision, or scale.

Returns

this

this builder instance for chaining.

Usecase

Configure output parameters when using the advanced .execute() API.

Example

context.procedure('usp_GenerateTrackingNumber')
  .withOutputParam('TrackingNumber', SqlType.NVarChar, { maxLength: 50 });

---

withInputOutputParam()

withInputOutputParam(name, value, type?, options?): this

Defined in: src/procedure/StoredProcedureBuilder.ts:634

Adds a bidirectional input/output (INOUT) parameter.

Parameters

Parameter	Type	Default value	Description
name	string	undefined	Parameter name.
value	unknown	undefined	Initial input value.
type	SqlType	SqlType.VarChar	Explicit SQL type.
options?	ParamOptions	undefined	Optional sizing options.

Returns

this

this builder instance for chaining.

Usecase

Use for procedures that take an initial value and mutate it in place (e.g. inout counter, state flag, or token).

Example

MySQL / MSSQL / PostgreSQL:

context.procedure('sp_increment_sequence')
  .withInputOutputParam('SequenceVal', 100, SqlType.Int);

---

withReturnValue()

withReturnValue(): this

Defined in: src/procedure/StoredProcedureBuilder.ts:670

Configures capturing of the procedure's integer return value (RETURN 0 or RETURN 1).

Returns

this

this builder instance for chaining.

Usecase

Capture status codes or error return codes returned via SQL Server / MySQL RETURN statements.

Example

SQL Server (MSSQL):

const result = await context.procedure('usp_ValidateUser')
  .input({ UserId: 10 })
  .withReturnValue()
  .execute();

if (result.returnValue === 0) {
  console.log('User is valid');
}

---

withTimeout()

withTimeout(ms): this

Defined in: src/procedure/StoredProcedureBuilder.ts:694

Sets command execution timeout for this stored procedure execution in milliseconds.

Parameters

Parameter	Type	Description
ms	number	Timeout in milliseconds.

Returns

this

this builder instance for chaining.

Usecase

Set higher timeouts for long-running batch or ETL stored procedures, or tight timeouts for interactive APIs.

Example

await context.procedure('usp_HeavyMonthlyBatch')
  .withTimeout(30000) // 30 seconds
  .run();

---

inTransaction()

inTransaction(tx): this

Defined in: src/procedure/StoredProcedureBuilder.ts:721

Binds the execution of this stored procedure to an active database transaction.

Parameters

Parameter	Type	Description
tx	DbTransaction	The active DbTransaction.

Returns

this

this builder instance for chaining.

Usecase

Execute stored procedures as part of a larger multi-step transaction or Unit of Work.

Example

await context.beginBoundedTransaction(async (tx) => {
  await context.procedure('usp_DebitAccount')
    .input({ AccountId: fromId, Amount: 100 })
    .inTransaction(tx)
    .run();

  await context.procedure('usp_CreditAccount')
    .input({ AccountId: toId, Amount: 100 })
    .inTransaction(tx)
    .run();
});

---

execute()

execute(): Promise<StoredProcedureResult<void>>

Defined in: src/procedure/StoredProcedureBuilder.ts:736

Executes the procedure with no expected record set, returning output params and return value.

Returns

Promise<StoredProcedureResult<void>>

A Promise resolving to StoredProcedureResult<void>.

Usecase

Core execution method for action procedures returning output parameters.

---

executeQuery()

executeQuery<T>(): Promise<StoredProcedureResult<T[]>>

Defined in: src/procedure/StoredProcedureBuilder.ts:765

Executes the procedure and returns a typed list of records along with output parameters and metadata.

Type Parameters

Type Parameter	Default type
T	unknown

Returns

Promise<StoredProcedureResult<T[]>>

A Promise resolving to StoredProcedureResult<T[]>.

Usecase

Core execution method for procedures returning a single tabular record set.

---

executeScalar()

executeScalar<T>(): Promise<T>

Defined in: src/procedure/StoredProcedureBuilder.ts:788

Executes the procedure and returns the first column of the first row.

Type Parameters

Type Parameter	Default type
T	unknown

Returns

Promise<T>

A Promise resolving to the scalar value.

Usecase

Core execution method for procedures returning a single scalar value.

---

executeMultiple()

executeMultiple<T>(): Promise<StoredProcedureResult<T>>

Defined in: src/procedure/StoredProcedureBuilder.ts:807

Executes the procedure and returns multiple typed record sets.

Type Parameters

Type Parameter	Default type
T extends unknown[]	unknown[]

Returns

Promise<StoredProcedureResult<T>>

A Promise resolving to StoredProcedureResult<T>.

Usecase

Core execution method for procedures returning multiple tables in a single call.