CODEWITHSUNDEEP
phpsql-serverlaravelstored-procedureslaravel-5
Jordan Davis
Jordan Davis

Reputation: 875

How to execute Stored Procedure from Laravel

I need to execute a stored procedure after my form submits data. I have the stored procedure working like I want it, and I have my form working properly. I just do not know the statement to execute the sp from laravel 5.

it should be something like this: execute my_stored_procedure. but I can not seem to find anything like that online.

Upvotes: 47

Views: 136293

Answers (14)

Божидар Пачовски
Божидар Пачовски

Reputation: 1

I am using Laravel v11.15.0 (PHP v8.3.9) and I want to execute a Stored Procedure on a remote server. I know that the connection to the remote server works because I tried it by calling the names of the databases (translate and comment in the code):

Route::get('/list-databases', function () {
    try {
        // Query to retrieve the names of all databases on the server with the specific connection
        $databases = DB::connection('sqlsrv_external')->select('SELECT name FROM sys.databases WHERE name NOT IN (\'master\', \'tempdb\', \'model\', \'msdb\');');

        // Returning the results as JSON
        return response()->json([
            'success' => true,
            'databases' => $databases,
        ]);
    } catch (\Exception $e) {
        // Handling connection errors
        return response()->json([
            'success' => false,
            'error' => $e->getMessage(),
        ], Response::HTTP_INTERNAL_SERVER_ERROR);
    }
});

My question is how to execute the query to this remote server using "DB::connection('sqlsrv_external')->select....... "?

This is my configuration that needs to execute a stored procedure on the remote server and write to the local (where the Laravel database is, the result)

```php
<?php

namespace App\Http\Controllers;

use Illuminate\Console\Command;
use Illuminate\Http\Request;
use App\Models\Payment; // I assume you are using the Payment model
use Illuminate\Support\Facades\DB;

use Log;

class SyncPaymentsController extends Controller
{
    public function index()
    {
        Log::info('Starting the synchronization process.');

        try {
            $results = $this->syncPayments();
            Log::info('Synchronization completed successfully.');

            // Passing the results to the view to be displayed to the user
            return view('sync-payments', ['status' => 'completed', 'results' => $results]);
        } catch (\Exception $e) {
            Log::error('SQL Query Failed', ['error' => $e->getMessage()]);

            return view('sync-payments', ['status' => 'failed', 'error' => $e->getMessage()]);
        }
    }

    private function syncPayments()
    {
        $fromDocDate = '2023-01-01 00:01:00';
        $toDocDate = '2024-07-09 00:00:00';

        try {
            $sql = "SET NOCOUNT ON; EXEC dbo.DOCUMENT_PAYMENT_WITH_PAYMENTDATE_PROFORMA_LARAVEL @fromDocDate = '2023-01-01 00:01:00', @toDocDate = '2024-07-09 00:00:00'";
            $results = DB::select($sql, [$fromDoc

Upvotes: 0

Avnish Tiwary
Avnish Tiwary

Reputation: 2276

MySql with Laravel 5.6(or above version may be)

DB::select(
    'call sp($id)'
);

Upvotes: 1

K. M. Shawkat Zamil
K. M. Shawkat Zamil

Reputation: 129

For multiple statements fetch from procedure, follow the following code:

$conn = DB::connection('sqlsrv');
    $sql = "exec [sp_name]";

    $pdo = $conn->getPdo()->prepare($sql);
    $pdo->execute();

    $res = array();
    do {
        array_push($res, $pdo->fetchAll());
       } while ($pdo->nextRowset());

    echo "<pre />";
    print_r($res);
    exit();

Upvotes: 0

marcincook
marcincook

Reputation: 1

# Real world from my Aplicaction Example Use

$result = DB::connection("sqlsrv")->statement("exec p_SaveOrderWithRelation  @cOrderID='{$order->id}', @cPaymentID='{$payment->id}', @cShiftID='{$shift->id}', @cSimple=0");
return $result;

# My PrimaryKey in Models is string type like, and i must put parametr ID like string type:

namespace App\Traits;
use Ramsey\Uuid\Uuid;
use Illuminate\Support\Str;

trait Uuids
{
    /**
     * Boot function from Laravel.
     */
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($model) {
            if (empty($model->{$model->getKeyName()})) {
                $model->{$model->getKeyName()} = Str::upper(Uuid::uuid4()->toString()); 
            }
        });
    }
    /**
     * Get the value indicating whether the IDs are incrementing.
     *
     * @return bool
     */
    public function getIncrementing()
    {
        return false;
    }
    /**
     * Get the auto-incrementing key type.
     *
     * @return string
     */
    public function getKeyType()
    {
        return 'string';
    }
}

Upvotes: 0

Gary
Gary

Reputation: 601

If your stored procedure always returns something then you can go with

DB::select("exec StoredProcedure '1','A','PARAM');

Otherwise (if there is no response from the SP) it will throw an exception. In that case I recommend using

DB::statetment("exec StoredProcedure '1','A','PARAM'");

Upvotes: 1

Faisal
Faisal

Reputation: 162

After a long research, this works:

DB::connection("sqlsrv")->statement('exec Pro_Internal_Transfer_Note_post @mvoucherid='.$VMID);

Upvotes: 2

user8268874
user8268874

Reputation:

Running the Microsoft SQL Server Stored Procedure (MS SQL Server) using PHP Laravel framework. If you are trying to run SP using Laravel Model then you can use following two approaches.

$submit = DB::select(" EXEC ReturnIdExample ?,?", array( $paramOne ,$paramTwo ) ); 

$submit = DB::select(" EXEC ReturnIdExample $paramOne,$paramTwo ");

If incase you are passing the Varchar Parameter then use the following:

$submit = DB::select(" EXEC ReturnIdExample '$paramOne', '$paramTwo' ");

If you are just passing parameter which are of INT or BIGINT then this should work and you can get the return from SP:

$submit = DB::select(" EXEC ReturnIdExample $paramOne,$paramTwo ");

Once the stored procedure is executed the values will be present in the $submit in the form of array, you need to loop through it and access the required columns.

foreach($submit  as $row)
{

echo $row->COLUMN1;
echo $row->COLUMN2;
echo $row->COLUMN3;

}

Upvotes: 4

DTIndia
DTIndia

Reputation: 11

Working code with Laraval 5.6,

DB::select('EXEC my_stored_procedure ?,?,?',['var1','var2','var3']);

Upvotes: 1

vanquan223
vanquan223

Reputation: 149

for Laravel 5.5

DB::select('call myStoredProcedure("p1", "p2")');

or

DB::select('call myStoredProcedure(?,?)',array($p1,$p2));

no parameter

DB::select('call myStoredProcedure()')

Upvotes: 7

zetta
zetta

Reputation: 954

app('db')->getPdo()->exec('exec my_stored_procedure');

Upvotes: 1

iMezied
iMezied

Reputation: 492

For version 5.5 use CALL:

return DB::select(DB::raw('call store_procedure_function(?)', [$parameter]))

Upvotes: 2

Pasindu Jayanath
Pasindu Jayanath

Reputation: 943

for Laravel 5.4

DB::select(DB::raw("exec my_stored_procedure"));

if you want to pass parameters:

DB::select(DB::raw("exec my_stored_procedure :Param1, :Param2"),[
    ':Param1' => $param_1,
    ':Param2' => $param_2,
]);

Upvotes: 8

Roque Mejos
Roque Mejos

Reputation: 457

You can also do this:

DB::select("CALL my_stored_procedure()");

Upvotes: 22

Pரதீப்
Pரதீப்

Reputation: 93704

Try something like this

DB::select('exec my_stored_procedure("Param1", "param2",..)');

or

DB::select('exec my_stored_procedure(?,?,..)',array($Param1,$param2));

Try this for without parameters 

DB::select('EXEC my_stored_procedure')

Upvotes: 64

Related Questions