CODEWITHSUNDEEP
phpsql-serverpdolaravellaravel-4
Priit
Priit

Reputation: 422

Laravel 4 mssql stored procedure with parameters as a prepared statements

I would like to format this working query as a prepared statement:

Route::get('procedure/{adr}/{part}/{batch?}', function($adr, $part, $batch= '') {

    return DB::connection('sqlconnection')->select("Procedure_Name '$adr', '$part', '$batch'");
});`

Tried everything I could think of and the closest I got was this.

DB::connection('sqlconnection')->select('Procedure_Name ?, ?, ?', array("'$adr'", "'$part'", "'$batch'"));

This results in a error with and sql command that when used directly on the db works just fine.

Illuminate \ Database \ QueryException SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Procedure_Name'. (SQL: Procedure_Name 'VLM', '7999800', 'P20131018-29')

What am I doing wrong?

Upvotes: 2

Views: 6145

Answers (2)

Kamaro
Kamaro

Reputation: 1015

I you are having error 7405 then do the following

Fixing Error-7405-Heterogeneous-queries-require-ANSI_NULLS

    DB::statement('SET ANSI_NULLS ON; SET ANSI_WARNINGS ON');

    $result = DB::select('EXEC sp_storeprocedure ' . DB::raw($param1) . ',' . DB::raw($param2) . ',' . DB::raw($param3) );

Upvotes: 0

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87719

You might find some trouble to execute them.

Here are some options:

DB::select('EXEC Procedure_Name ?,?,?',array($adr,$part,$batch));

Or 

DB::statement('EXEC Procedure_Name' . DB::raw($adr) . ',' . DB::raw($part) . ',' . DB::raw($batch) );

And, this is a quick and dirty way to do it:

$db = DB::connection();

$stmt = $db->pdo->prepare("EXEC Procedure_Name ?,?,?");

$stmt->bindParam(1, $adr);
$stmt->bindParam(2, $part);
$stmt->bindParam(3, $batch);
$stmt->execute();

$search = array();
do {
    $search = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');
} while ($stmt->nextRowset());

Upvotes: 8

Related Questions