How to execute Stored Procedure from Laravel

Question

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.

Exhaustion

Possible duplicate of How to call stored procedure on Laravel? – Incipient

Answer 1

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')

Cathrinecathryn

I do not need to return anything, and my stored procedure does not need any parameters. so could I do DB::select('my_stored_procedure') ? – Exhaustion

@JordanDavis - check now – Beardsley

Thanks, it now tries to run the SP, however it looks like my server does not have the needed permissions to do so – Exhaustion

Its your login which don't have permission to execute any thing in Server. Contact your DBA – Beardsley

@JordanDavis If you don't need anything returned from the stored procedure, you can also use DB::statement instead of DB::select – Aculeate

my error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXEC rule_4' at line 1 (SQL: EXEC rule_4) – Farly

@Cathrinecathryn Maybe you can help me. Look at this : #51839422 – Aerostat

Answer 2

You can also do this:

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

Nitrification

Answer 3

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,
]);

Athalee

Answer 4

for Laravel 5.5

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

or

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

no parameter

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

Ranchod

Answer 5

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;

}

Loquacious

Answer 6

For version 5.5 use CALL:

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

Prague

Answer 7

After a long research, this works:

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

Sprang

Answer 8

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

Anteversion

Is this laravel answer? – Droplight

While this may answer the question it's better to add some description on how this answer may help to solve the issue. Please read How do I write a good answer to know more. – Brimmer

@MunimMunna of course it is, app() is a Laravel Helper. – Anteversion

Answer 9

Working code with Laraval 5.6,

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

Pileate

Answer 10

MySql with Laravel 5.6(or above version may be)

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

Jerrylee

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value – Bumf

@Bumf Thanks for your suggestion. I've added the scenario to use it. – Jerrylee

Answer 11

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'");

Balkan

Answer 12

# 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';
    }
}

Alchemize

Answer 13

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();

Commensurate

Answer 14

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

Pyrolysis

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Appreciate