How to create stored procedure laravel

Question

Hi I've been looking around for some kind of how to guide for stored procedures in Laravel but can't find any so far. My issue is basically I have a huge list of parameters that I need to send to the stored procedure called InsertNewApplicant but I'm not sure how to build the query.

This is all I have so far, I'm not sure where I chose which database to send it to or how to make the connection to that database.

Any help would be greatly appreciated

 $result = DB::select('call InsertNewApplicant(?????????????????????????)',
                    array($firstName, $middleName, $addressLine_1, $addressLine_2, $postCode, $landline, $mobile, $email,
                    $DOB, $maritalStatus, $industry, $occupation, $jobTitle, $selfEmployed, $selfAssessment, $workHome,
                    $ownTransport, $companyVehicle, $paySubs, $otherIncome, $printForms, $marketingUs, $marketingOther,
                    $agreedTNCs, $TNCVersion, $CampaignSource));

Here's my two databases - I need to send this data to the sqlserv database so I'm not sure what to do

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'SECRET'),
    'port' => env('DB_PORT', 'SECRET'),
    'database' => env('DB_DATABASE', 'SECRET'),
    'username' => env('DB_USERNAME', 'SECRET'),
    'password' => env('DB_PASSWORD', 'SECRET'),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],
'sqlsrv' => array(
    'driver' => 'sqlsrv',
    'host' => ' AN IP ADDRESS', // Provide IP address here
    'database' => 'SECRET',
    'username' => 'SECRET',
    'password' => 'SECRET',
    'prefix' => '',
),

David Smith · Accepted Answer · 2016-06-28T15:46:15.743

Your first task is to configure your application to connect to the database that your stored procedure is in. There is plenty of documentation on the Laravel website that will guide you towards how to do this: https://laravel.com/docs/5.1/database

A quick clue is to look inside your app/config/database.php file. It should be fairly obvious what to do once you look at that file.

Once you've configured your database, make sure to select the correct one using the DB::connection() method. In your case, you would want to do: $conn = DB::connection('sqlsrv') to get the connection.

Once you have added your database connection configuration, you should call your stored procedure by calling the DB::select() method. Just remember to include commas between each of the parameter placeholders (question marks):

$conn = DB::connection('sqlsrv');
$result = $conn->select('call InsertNewApplicant(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
  [
    $firstName, $middleName, $addressLine_1, $addressLine_2, $postCode, 
    $landline, $mobile, $email, $DOB, $maritalStatus, $industry, 
    $occupation, $jobTitle, $selfEmployed, $selfAssessment, $workHome, 
    $ownTransport, $companyVehicle, $paySubs, $otherIncome, 
    $printForms, $marketingUs, $marketingOther, $agreedTNCs, 
    $TNCVersion, $CampaignSource
  ]
);

How would i do an insert? i realized that I meant to do that, i'll update the question in a second with the database details — GingerFish, Jun 28 '16 at 15:31
Your original question implies that the stored procedure is what is performing the actual insert. You're then selecting the result of that stored procedure with the `DB::select()` method. If you want to do an insert to the database directly from PHP, you can use the `DB::insert()` method, which you can read more about here: https://laravel.com/docs/5.1/database (look for "Running An Insert Statement") — David Smith, Jun 28 '16 at 15:42
@David Smith 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 07:55

How to create stored procedure laravel

1 Answers1