There are two major problems with your code
DELIMITER
is not a valid sql statement. It’s just a MySql client command. So just don’t use it. BTW the error you get tells you exactly that.- You can’t use
DB::statement
to executeCREATE PROCEDURE
code because it uses prepared statement source code forConnection
. You can use PDOexec()
DB::connection()->getPdo()->exec()
instead
That being said a sample migration for imaginary tags
table might look like this
class CreateTagsTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('tags', function($table){
$table->increments('id');
$table->string('name')->unique();
});
$sql = <<<SQL
DROP PROCEDURE IF EXISTS sp_insert_tag;
CREATE PROCEDURE sp_insert_tag(IN _name VARCHAR(32))
BEGIN
INSERT INTO `tags`(`name`) VALUES(_name);
END
SQL;
DB::connection()->getPdo()->exec($sql);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
$sql = "DROP PROCEDURE IF EXISTS sp_insert_tag";
DB::connection()->getPdo()->exec($sql);
Schema::drop('tags');
}
}