Running Stored Procedures

Think that for execute a sp you could use DB::sentence("EXEC dbo.uspMyProcedure(1, 2, 3);").

Hope this helps!

Also, check this article about raw queries by chris fidao: http://fideloper.com/laravel-raw-queries

Thank you for the input. I'm actually not having any issues running a stored procedure in Laravel. My problem is getting the out parameters after the stored procedure runs. In my example, $returnId and $wasTableUpdated are supposed to be updated by the stored procedure but it's not working. Everything I've ready online says binding the out parameters like I have is supposed to do the trick, but I can't get it to work.

Have you tried doing the same thing with just PDO and not the query builder? It may be something specific to PDO and/or SQL Server and not Laravel-related at all.

Yes, I have. And sadly, that isn't working either.

 \DB::select('EXEC dbo.prUpdatePostScoreAndCompDate ?, ?, ?, ?, ?', array($trainingRecordId,$score,$completionDate,$returnId,$wasTableUpdated));

try this way . not tested yet...

I've always found with PHP and MSSQL the driver installed matters a lot! Also typical the recordset(s) returned need to be closed to then be able to access any return or input/output parameters

Try this, output parameters &$returnId

$stmt->bindParam(4, &$returnId, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(5, &$wasTableUpdated, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);

I would be interested in seeing a solution to this as well. Did any of the above end up working for you OP?

No, none of the above worked. You can't pass in &$returnId because you get this error: "Call-time pass-by-reference has been removed."

What does your stored procedure look like?

Check this http://php.net/manual/es/pdo.prepared-statements.php

Thank you :)

Just to return to this, I found adding $stmt->nextRowset(); after the execute line then released the output parameters in the stored procedure I was working with. Also ensure SET NOCOUNT ON is within the stored procedure.

ritey, how did you define the output parameters in your stored procedure? Thanks much.

I tried below and it works for me. $statement = DB::connection()->getPdo()->prepare("CALL sp_updateOwnerDetails(?)"); $statement->bindParam(1, $id);

You need to do changes like below $stmt = $db->getPdo()->prepare("CALL prUpdatePostScoreAndCompDate ?, ?, ?, ?, ?");

$data = DB::select("SET NOCOUNT ON exec dbo.mystoredprocedure ?,?,?;", ['20180101', '20180130','G']);

I used this way and obtained results

Hi, maybe you could find the solution ?, I am having the same problem.

Currently tested in laravel 6.*, this is the solution, add the following instruction "SET NOCOUNT ON" before executing the stored procedure, however I find it necessary to pass a variable with faker value in my case null, in the position of the output parameter.

CREATE PROCEDURE sp_gestion_clientes
@Id INT, 
@Nombres VARCHAR(50),
@Cedula VARCHAR(13),
@Direccion VARCHAR(150),
@Option VARCHAR(10),  
@Mensaje VARCHAR(255) OUT
AS
BEGIN TRY
    BEGIN TRAN

    IF @Option = 'INSERT'
    BEGIN
        INSERT Cliente VALUES(@Nombres, @Cedula, @Direccion)
        SET @Mensaje = 'Se insertó el registro exitosamente'
    END

    SELECT @Mensaje AS 'Mensaje'
...

$data = \Illuminate\Support\Facades\DB::select('SET NOCOUNT ON exec sp_gestion_clientes ?, ?, ?, ?, ?, ?', array('0', 'Luis Arce', '1234567890', 'Ecuador', 'INSERT', null));

dd($data);

The result is as follows

array:1 [▼
  0 => {#246 ▼
    +"Mensaje": "Se insertó el registro exitosamente"
  }
]

For this test procedure:

CREATE PROCEDURE testOutput
  @param uniqueidentifier OUTPUT
AS
BEGIN
   SET @param = NEWID()
END

working solution:

$query = '
   DECLARE @id uniqueidentifier
   EXEC testOutput @id output
   select @id as id
';

DB::select($query);

output:

array:1 [
  0 => {#452
    +"id": "48B0175E-7FD6-481E-A2D8-819FCEB83BEC"
  }
]