PHP code example of magslabs / laravel-storedproc

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

magslabs / laravel-storedproc example snippets

// Using the Facade (alias registered automatically)
use StoredProcedure;

$result = StoredProcedure::stored_procedure('get_user_by_id')
    ->stored_procedure_params([':id'])
    ->stored_procedure_values([1])
    ->execute()
    ->stored_procedure_result();

// Shorter aliases (same behavior)
$result = StoredProcedure::procedure('get_user_by_id')
    ->params([':id'])
    ->values([1])
    ->run()
    ->result();

use MagsLabs\LaravelStoredProc\StoredProcedure;

$result = StoredProcedure::stored_procedure('get_user_by_id')
    ->stored_procedure_params([':id'])
    ->stored_procedure_values([1])
    ->execute()
    ->stored_procedure_result();

$result = StoredProcedure::stored_procedure('get_all_users')
    ->execute()
    ->stored_procedure_result();

// Get paginated results
$users = StoredProcedure::stored_procedure('get_all_users')
    ->execute()
    ->stored_procedure_result()
    ->paginate(15); // 15 items per page

// SQL Server stored procedure with OUTPUT parameters
$result = StoredProcedure::stored_procedure('sp_get_user_stats')
    ->stored_procedure_params([
        ':user_id',
        '@total_count OUTPUT'
    ])
    ->stored_procedure_values([123]) // Only input values
    ->stored_procedure_output_params(['@total_count' => 'INT'])
    ->execute()
    ->stored_procedure_result();

// Access both dataset and OUTPUT parameters
$users = $result->result;        // Laravel Collection
$totalCount = $result->output;   // Scalar value

// MySQL stored procedure with OUT parameters
$result = StoredProcedure::stored_procedure('get_user_stats')
    ->stored_procedure_params([
        ':user_id',
        '@total_count'
    ])
    ->stored_procedure_values([123]) // Only input values
    ->stored_procedure_output_params(['@total_count' => 'INT'])
    ->execute()
    ->stored_procedure_result();

// Access both dataset and OUT parameters
$users = $result->result;        // Laravel Collection
$totalCount = $result->output;   // Scalar value

$result = StoredProcedure::procedure('get_user_by_id')
    ->params([':id'])
    ->values([1])
    ->validate()  // checks existence + input/output parameter counts
    ->run()
    ->result();

// Throws StoredProcedureNotFoundException if missing
StoredProcedure::procedure('get_users')->assertExists();

// Returns true/false without throwing
if (StoredProcedure::procedure('get_users')->exists()) {
    // ...
}

// Qualified name in the procedure itself
StoredProcedure::procedure('dbo.sp_get_users')->run()->result();

// Or set schema separately
StoredProcedure::procedure('sp_get_users')
    ->stored_procedure_schema('custom_schema')
    ->run()
    ->result();

// As an array
->stored_procedure_params([':id'])
->stored_procedure_values([1]);

// From a Laravel Request or FormRequest
->stored_procedure_params($request); // extracts keys and formats as placeholders
->stored_procedure_values([$request->id]);

->with_transaction()

// Define OUTPUT parameters in stored_procedure_params with OUTPUT keyword
$result = StoredProcedure::stored_procedure('sp_get_user_stats')
    ->stored_procedure_params([
        ':user_id',
        '@total_users OUTPUT',
        '@active_users OUTPUT',
        '@message OUTPUT'
    ])
    ->stored_procedure_values([123]) // Only input values, OUTPUT params are handled automatically
    ->stored_procedure_output_params([
        '@total_users' => 'INT',
        '@active_users' => 'INT',
        '@message' => 'VARCHAR(255)'
    ])
    ->execute()
    ->stored_procedure_result();

// Access the results
echo $result->output->total_users;  // OUTPUT parameter value
echo $result->output->active_users; // OUTPUT parameter value
echo $result->output->message;      // OUTPUT parameter value
echo $result->result;               // Regular dataset (if any)

// Define OUT parameters in stored_procedure_params (clean syntax)
$result = StoredProcedure::stored_procedure('get_user_stats')
    ->stored_procedure_params([
        ':user_id',
        '@total_users',
        '@active_users',
        '@message'
    ])
    ->stored_procedure_values([123]) // Only input values, OUT params are handled automatically
    ->stored_procedure_output_params([
        '@total_users' => 'INT',
        '@active_users' => 'INT',
        '@message' => 'VARCHAR(255)'
    ])
    ->execute()
    ->stored_procedure_result();

// Access the results
echo $result->output->total_users;  // OUT parameter value
echo $result->output->active_users; // OUT parameter value
echo $result->output->message;      // OUT parameter value
echo $result->result;               // Regular dataset (if any)

->stored_procedure_params([
    ':api_service_id',
    ':statuscode',
    '@result OUTPUT',
    '@message OUTPUT'
])

->stored_procedure_params([
    ':api_service_id',
    ':statuscode',
    '@result',
    '@message'
])

> ->stored_procedure_params([
>     ':api_service_id',
>     ':statuscode',
>     '@result OUTPUT',
> ])
> 

// Simple array (defaults to BIT type for SQL Server, INT for MySQL)
->stored_procedure_output_params(['@result', '@status'])

// Associative array with specific types
->stored_procedure_output_params([
    '@result' => 'INT',
    '@message' => 'VARCHAR(255)',
    '@success' => 'BIT',        // SQL Server
    '@success' => 'TINYINT',    // MySQL equivalent
    '@created_date' => 'DATETIME'
])

->stored_procedure_values([$apiServiceId, $statusCode]) // OUTPUT/OUT params handled automatically

$response = StoredProcedure::stored_procedure('sp_complex_operation')
    ->stored_procedure_output_params(['@status' => 'BIT'])
    ->execute()
    ->stored_procedure_result();

// Single OUTPUT parameter → scalar value
$status = $response->output; // Direct scalar value

// Multiple OUTPUT parameters → object
$status = $response->output->status;
$message = $response->output->message;

// Regular dataset
$data = $response->result; // Laravel Collection

// Get paginated results from a stored procedure
$users = StoredProcedure::stored_procedure('get_users_paginated')
    ->stored_procedure_params([':page', ':per_page'])
    ->stored_procedure_values([1, 15])
    ->execute()
    ->stored_procedure_result()
    ->paginate(15); // 15 items per page

// Use in your controller
return view('users.index', compact('users'));

// Custom pagination options
$results = $storedProcResult->paginate(
    $perPage = 20,        // Items per page
    $page = 2,            // Current page (optional)
    $pageName = 'page'    // Page parameter name (optional)
);

// In your Blade template
{{ $results->links() }}

use MagsLabs\LaravelStoredProc\StoredProcedure;

// Basic usage with parameters and transaction
$users = StoredProcedure::stored_procedure('get_users_by_role')
    ->stored_procedure_connection('mysql') // Optional
    ->stored_procedure_params([':role'])
    ->stored_procedure_values(['admin'])
    ->with_transaction() // Optional
    ->execute()
    ->stored_procedure_result();

// With pagination
$paginatedUsers = StoredProcedure::stored_procedure('get_users_by_role')
    ->stored_procedure_params([':role'])
    ->stored_procedure_values(['admin'])
    ->execute()
    ->stored_procedure_result()
    ->paginate(20); // 20 items per page

// SQL Server with OUTPUT parameters
$stats = StoredProcedure::stored_procedure('sp_get_user_statistics')
    ->stored_procedure_connection('sqlsrv') // SQL Server connection
    ->stored_procedure_params([':user_id'])
    ->stored_procedure_values([123])
    ->stored_procedure_output_params([
        '@total_posts' => 'INT',
        '@is_active' => 'BIT',
        '@last_login' => 'DATETIME'
    ])
    ->execute()
    ->stored_procedure_result();

// MySQL with OUT parameters
$stats = StoredProcedure::stored_procedure('get_user_statistics')
    ->stored_procedure_connection('mysql') // MySQL connection
    ->stored_procedure_params([':user_id'])
    ->stored_procedure_values([123])
    ->stored_procedure_output_params([
        '@total_posts' => 'INT',
        '@is_active' => 'TINYINT',
        '@last_login' => 'DATETIME'
    ])
    ->execute()
    ->stored_procedure_result();

// Access results
$userData = $stats->result;           // Dataset as Collection
$totalPosts = $stats->output->total_posts;  // OUTPUT/OUT parameter
$isActive = $stats->output->is_active;      // OUTPUT/OUT parameter
$lastLogin = $stats->output->last_login;    // OUTPUT/OUT parameter

// Example via dependency injection in a controller
class UserController extends Controller
{
    protected StoredProcedure $storedProc;

    public function __construct(StoredProcedure $storedProc)
    {
        $this->storedProc = $storedProc;
    }

    public function index(Request $request)
    {
        $users = $this->storedProc->stored_procedure('get_users_by_role')
            ->stored_procedure_connection('mysql')
            ->stored_procedure_params([':role'])
            ->stored_procedure_values([$request->role])
            ->with_transaction()
            ->execute()
            ->stored_procedure_result()
            ->paginate(15);

        return view('users.index', compact('users'));
    }

    public function stats($userId)
    {
        $stats = $this->storedProc->stored_procedure('sp_get_user_statistics')
            ->stored_procedure_connection('sqlsrv')
            ->stored_procedure_params([
                ':user_id',
                '@total_posts OUTPUT'
            ])
            ->stored_procedure_values([$userId])
            ->stored_procedure_output_params(['@total_posts' => 'INT'])
            ->execute()
            ->stored_procedure_result();

        return response()->json([
            'user_data' => $stats->result,
            'total_posts' => $stats->output
        ]);
    }
}

->stored_procedure_connection('your_own_connection_database_name')

// SQL Server stored procedure with multiple OUTPUT parameters
$result = StoredProcedure::stored_procedure('sp_complex_user_operation')
    ->stored_procedure_connection('sqlsrv')
    ->stored_procedure_params([
        ':user_id',
        ':action',
        '@rows_affected OUTPUT',
        '@success OUTPUT',
        '@message OUTPUT',
        '@execution_time OUTPUT'
    ])
    ->stored_procedure_values([123, 'update_profile']) // Only input values
    ->stored_procedure_output_params([
        '@rows_affected' => 'INT',
        '@success' => 'BIT',
        '@message' => 'VARCHAR(500)',
        '@execution_time' => 'FLOAT'
    ])
    ->with_transaction()
    ->execute()
    ->stored_procedure_result();

// Access all results
$userData = $result->result;                    // Dataset
$rowsAffected = $result->output->rows_affected; // OUTPUT parameter
$success = $result->output->success;            // OUTPUT parameter
$message = $result->output->message;            // OUTPUT parameter
$executionTime = $result->output->execution_time; // OUTPUT parameter

// MySQL stored procedure with multiple OUT parameters
$result = StoredProcedure::stored_procedure('complex_user_operation')
    ->stored_procedure_connection('mysql')
    ->stored_procedure_params([
        ':user_id',
        ':action',
        '@rows_affected',
        '@success',
        '@message',
        '@execution_time'
    ])
    ->stored_procedure_values([123, 'update_profile']) // Only input values
    ->stored_procedure_output_params([
        '@rows_affected' => 'INT',
        '@success' => 'TINYINT',
        '@message' => 'VARCHAR(500)',
        '@execution_time' => 'DECIMAL(10,3)'
    ])
    ->with_transaction()
    ->execute()
    ->stored_procedure_result();

// Access all results
$userData = $result->result;                    // Dataset
$rowsAffected = $result->output->rows_affected; // OUT parameter
$success = $result->output->success;            // OUT parameter
$message = $result->output->message;            // OUT parameter
$executionTime = $result->output->execution_time; // OUT parameter

// Advanced pagination with custom options
$users = StoredProcedure::stored_procedure('get_users_with_filters')
    ->stored_procedure_params([':role', ':status', ':search'])
    ->stored_procedure_values(['admin', 'active', $searchTerm])
    ->execute()
    ->stored_procedure_result()
    ->paginate(
        $perPage = 25,           // Items per page
        $page = $request->page,   // Current page
        $pageName = 'users_page' // Custom page parameter name
    );

// In Blade template
{{ $users->appends(request()->query())->links('custom.pagination') }}

use MagsLabs\LaravelStoredProc\Exceptions\InvalidCallOrderException;
use MagsLabs\LaravelStoredProc\Exceptions\ParameterMismatchException;
use MagsLabs\LaravelStoredProc\Exceptions\StoredProcedureNotFoundException;

try {
    $result = StoredProcedure::procedure('risky_operation')
        ->params([':data'])
        ->values([$complexData])
        ->validate()
        ->with_transaction()
        ->run()
        ->result();

    return response()->json([
        'success' => true,
        'data' => $result,
    ]);
} catch (StoredProcedureNotFoundException $e) {
    return response()->json(['message' => 'Procedure not found'], 404);
} catch (ParameterMismatchException $e) {
    return response()->json(['message' => $e->getMessage()], 422);
} catch (InvalidCallOrderException $e) {
    return response()->json(['message' => $e->getMessage()], 500);
} catch (Exception $e) {
    // Execution failures are logged to the dedicated log channel
    return response()->json([
        'success' => false,
        'message' => 'Operation failed',
        'error' => $e->getMessage(),
    ], 500);
}

class UserService
{
    protected StoredProcedure $storedProc;

    public function __construct(StoredProcedure $storedProc)
    {
        $this->storedProc = $storedProc;
    }

    public function getUserStats($userId)
    {
        return $this->storedProc
            ->stored_procedure('sp_get_user_stats')
            ->stored_procedure_params([
                ':user_id',
                '@total_posts OUTPUT'
            ])
            ->stored_procedure_values([$userId])
            ->stored_procedure_output_params(['@total_posts' => 'INT'])
            ->execute()
            ->stored_procedure_result();
    }

    public function updateUserProfile($userId, $data)
    {
        return $this->storedProc
            ->stored_procedure('sp_update_user_profile')
            ->stored_procedure_params([':user_id', ':name', ':email'])
            ->stored_procedure_values([$userId, $data['name'], $data['email']])
            ->with_transaction()
            ->execute()
            ->stored_procedure_result();
    }
}

'channels' => [

    // other log channels...

    'magslabs_laravel_stored_proc' => [
        'driver' => 'single',
        'path' => storage_path('logs/magslabs_laravel_stored_proc.log'),
        'level' => 'debug',
    ],
],

bash
php artisan vendor:publish --tag=storedproc-config

bash
php artisan vendor:publish --tag=storedproc-config