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.

Possible duplicate of [How to call stored procedure on Laravel?](http://stackoverflow.com/questions/30498227/how-to-call-stored-procedure-on-laravel) — TIGER, Feb 04 '17 at 08:12

Pரதீப் · Accepted Answer · 2015-12-28T16:42:46.823

63

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

edited Dec 28 '15 at 16:42

answered Dec 28 '15 at 16:31

Pரதீப்

91,748
19
131
172

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') ? – Jordan Davis Dec 28 '15 at 16:37
@JordanDavis - check now – Pரதீப் Dec 28 '15 at 16:40
Thanks, it now tries to run the SP, however it looks like my server does not have the needed permissions to do so – Jordan Davis Dec 28 '15 at 17:15
1

Its your `login` which don't have permission to `execute` any thing in `Server`. Contact your DBA – Pரதீப் Dec 28 '15 at 17:16
4

@JordanDavis If you don't need anything returned from the stored procedure, you can also use DB::statement instead of DB::select – Rogier Mar 25 '16 at 13:38
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)` – KD.S.T. Apr 18 '18 at 07:35
@Pரதீப் Maybe you can help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 15 '18 at 02:28

score 21 · Answer 2 · edited Nov 25 '17 at 09:21

21

You can also do this:

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

edited Nov 25 '17 at 09:21

Divyang Desai

7,483
13
50
76

answered Sep 01 '16 at 03:23

Roque Mejos

347
2
10

score 8 · Answer 3 · answered May 23 '18 at 11:19

8

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

answered May 23 '18 at 11:19

Pasindu Jayanath

892
10
27

vanquan223 · Answer 4 · 2018-08-09T09:13:30.633

7

for Laravel 5.5

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

or

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

no parameter

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

edited Aug 09 '18 at 09:13

answered Aug 09 '18 at 09:12

vanquan223

139
2
5

score 4 · Answer 5 · 2018-09-02T05:33:13.680

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;

}

score 2 · Answer 6 · edited Jun 02 '18 at 19:58

2

For version 5.5 use CALL:

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

edited Jun 02 '18 at 19:58

Tarun Dhiman

3
2

answered Feb 11 '18 at 11:35

iMezied

483
8
13

score 2 · Answer 7 · edited Aug 27 '19 at 10:42

2

After a long research, this works:

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

edited Aug 27 '19 at 10:42

gorjan

5,405
2
20
40

answered Apr 22 '19 at 06:57

Faisal

152
1
1
12

score 1 · Answer 8 · answered Jun 08 '18 at 02:11

1

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

answered Jun 08 '18 at 02:11

zetta

944
6
4

1

Is this laravel answer? – Munim Munna Jun 08 '18 at 02:35
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](https://stackoverflow.com/help/how-to-answer) to know more. – Roshana Pitigala Jun 08 '18 at 04:35
1

@MunimMunna of course it is, app() is a Laravel Helper. – zetta Oct 27 '18 at 05:48

score 1 · Answer 9 · edited Aug 09 '18 at 09:13

1

Working code with Laraval 5.6,

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

edited Aug 09 '18 at 09:13

Suraj Rao

29,388
11
94
103

answered Aug 06 '18 at 05:04

DTIndia

11
2

score 1 · Answer 10 · edited Feb 16 '23 at 10:04

1

MySql with Laravel 5.6(or above version may be)

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

edited Feb 16 '23 at 10:04

TarangP

2,711
5
20
41

answered Jan 25 '20 at 14:25

Avnish Tiwary

2,188
22
27

1

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 – byaruhaf Jan 25 '20 at 15:25
@byaruhaf Thanks for your suggestion. I've added the scenario to use it. – Avnish Tiwary Jan 27 '20 at 10:14

score 1 · Answer 11 · answered Oct 02 '20 at 20:52

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

score 0 · Answer 12 · answered Feb 02 '21 at 22:16

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

score 0 · Answer 13 · answered Jul 13 '22 at 06:59

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

How to execute Stored Procedure from Laravel

13 Answers13

Linked