Download the PHP package magslabs/laravel-storedproc without Composer

Laravel-Storedproc

Laravel-Storedproc is a fluent wrapper for calling stored procedures in Laravel — because not every query belongs in Eloquent.

In many real-world applications, especially enterprise systems, stored procedures are a key part of the backend. Laravel doesn’t natively support them in an elegant way, so most developers are stuck writing raw DB::select() queries over and over.

This package simplifies that.

Features

• Fluent, chainable syntax for stored procedure calls (with shorter method aliases)
• Parameter binding from arrays or Laravel requests
• Optional Laravel-managed transaction support with automatic rollback on failure
• Connection-aware drivers for MySQL and SQL Server (CALL / EXEC resolved per connection)
• Automatic existence check before every execute() / run() (v2 default; configurable)
• Opt-in parameter validation against the database schema
• OUTPUT/OUT parameter support for SQL Server and MySQL stored procedures
• Built-in pagination macro for Laravel Collections
• Schema qualification (dbo.sp_users, database.procedure, or stored_procedure_schema())
• Enhanced logging with dedicated log channel
• Smart result handling (datasets + OUTPUT parameters)
• Typed exceptions for missing procedures, parameter mismatches, and invalid call order

Installation

You can install the package via Composer:

The package will automatically register the StoredProcedureServiceProvider (binding), PaginationServiceProvider (Collection paginate macro), and the StoredProcedure facade alias.

Configuration (optional)

Publish the config file to customize validation and schema defaults:

This creates config/storedproc.php. You can also set these via .env:

Basic Usage

You can use the Facade (when installed in a Laravel app) or the class directly:

Or use the class directly:

Stored procedure without parameters:

With Pagination

With OUTPUT Parameters (SQL Server & MySQL)

SQL Server Example:

MySQL Example:

The result is returned as a Laravel Collection for easy chaining and manipulation, or as an object with result and output properties when OUTPUT parameters are used.

Method Aliases

All original method names remain supported. Shorter aliases are available for the same behavior:

Validation & Existence Checks (v2)

Starting in v2.0.0, every execute() / run() automatically verifies the procedure exists on the database before running it. Disable this to restore v1 behavior:

Full parameter validation

Opt in per call with validate(), or globally via .env:

When validation fails, a ParameterMismatchException is thrown with a message listing what the database expects vs. what you provided.

Check existence without executing

Schema qualification

Unqualified names are resolved automatically:

• SQL Server: prefixed with dbo (or STORED_PROC_SCHEMA)
• MySQL: prefixed with the connection database name

Override explicitly:

On SQL Server, set STORED_PROC_CHECK_SYNONYMS=true if you call procedures via synonyms and want them to pass existence checks.

Parameters & Values

You can pass parameters in multiple formats:

Transaction Support

Enable Laravel-managed transactions like so:

Laravel will automatically commit on success or roll back if the procedure throws an error.

Warning: Use this only if your stored procedure does not manage its own transactions (BEGIN, COMMIT, etc.).

OUTPUT Parameters (SQL Server & MySQL)

For stored procedures that return OUTPUT/OUT parameters, you can capture them using the stored_procedure_output_params() method:

SQL Server OUTPUT Parameters

MySQL OUT Parameters

OUTPUT/OUT Parameter Usage

Step 1: Include OUTPUT/OUT parameters in stored_procedure_params() with the appropriate keyword:

For SQL Server:

For MySQL:

💡 Example from your use case:

Step 2: Define SQL types in stored_procedure_output_params():

Step 3: Only provide input values in stored_procedure_values():

Smart Result Handling

The package automatically detects if OUTPUT/OUT parameters are used and returns an object with both result (dataset) and output (parameters):

Pagination Support

The package includes a built-in pagination macro for Laravel Collections, making it easy to paginate stored procedure results:

Pagination Features

• Automatic URL handling: Uses current request URL and query parameters
• Laravel pagination: Full compatibility with Laravel's pagination system
• Customizable: Set custom page name and per-page count
• Blade integration: Works seamlessly with Laravel's pagination views

Example: Full Workflow

This is useful when you want to inject the instance or reuse it across multiple calls. The instance auto-resets after result(); call reset() manually if you need to clear state earlier.

Switching Database Connections

Need to call a stored procedure on a different connection/database?

This uses Laravel’s connection from config/database.php.

Common Gotchas

• You must call methods in this order:

  1. stored_procedure() / procedure() (required)
  2. stored_procedure_connection() / connection() (optional)
  3. stored_procedure_schema() (optional)
  4. stored_procedure_params() / params() (optional, if your proc has parameters)
  5. stored_procedure_values() / values() (required if you set params)
  6. stored_procedure_output_params() / outputs() (optional, SQL Server & MySQL)
  7. validate() or assertExists() (optional)
  8. with_transaction() (optional)
  9. execute() / run() (required)
  10. stored_procedure_result() / result() (required)
• All input parameters must be bound by position in the stored_procedure_values() array.
• OUTPUT/OUT parameters must be included in stored_procedure_params() with clean syntax:
  • SQL Server: '@result OUTPUT' (OUTPUT keyword required)
  • MySQL: '@result' (clean syntax, no OUT keyword needed)
• OUTPUT/OUT parameters are supported on both SQL Server and MySQL databases.
• When using OUTPUT/OUT parameters, the result will be an object with result and output properties.
• Pagination works on the returned Collection, so call paginate() after stored_procedure_result().
• After calling result(), the fluent instance auto-resets so it can be reused safely via dependency injection.
• v2 existence checks run before execution by default. Set STORED_PROC_CHECK_EXISTS=false if you need v1 behavior.
• The StoredProcedureServiceProvider registers the StoredProcedure binding; the PaginationServiceProvider registers the paginate() macro on Collection, so both are available after installation.
• The StoredProcedure facade alias is registered automatically so you can use StoredProcedure::stored_procedure('name') statically in your app.
• Logging: Bound values and output params are logged only at debug level to avoid exposing sensitive data in production logs.

Compatibility

• PHP: 8.0, 8.1, 8.2, 8.3, 8.4 (Laravel 13 requires PHP 8.3+)
• Laravel: 9.x, 10.x, 11.x, 12.x, 13.x (backward compatible; 13.x is the default target)
• Databases:
  • MySQL (5.7+, 8.0+)
  • SQL Server (2016+, 2019+, 2022+)
  • Other databases are not officially supported and may not work as expected

Feature Support by Database

Upgrading from v1 to v2

v2.0.0 is backward compatible — all v1 method names and chains still work.

Breaking-adjacent changes to be aware of:

1. Existence checks are on by default. Every execute() / run() verifies the procedure exists before calling it. Set STORED_PROC_CHECK_EXISTS=false to disable.
2. Call-order errors now throw InvalidCallOrderException (still extends Exception).
3. Execution is driver-based. CALL vs EXEC is resolved from the active connection, not a global default.

Publish config when you want to tune validation or schema defaults:

See CHANGELOG.md for the full v2 release notes.

Advanced Usage

Complex Stored Procedure with Multiple OUTPUT/OUT Parameters

SQL Server Example:

MySQL Example:

Pagination with Custom Options

Error Handling and Logging

The package throws typed exceptions you can catch individually:

All extend StoredProcedureException, which extends PHP's Exception.

Reusing StoredProcedure Instances

Logging Stored Procedure Executions

This package includes built-in logging to help trace and debug stored procedure execution.

Enable a Custom Log File

To log all stored procedure operations into a dedicated log file, add the following channel to your Laravel app’s config/logging.php:

License

MIT License. © Mark Angelo Sollano / magslabs

Credits

Created by @masollano — magslabs/laravel-storedproc