Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I need to create a custom mysql 8 procedure and filling code in dBeaver
CREATE PROCEDURE `getBranchesWithDepartments` ( branchId smallint unsigned )
SQL SECURITY DEFINER
COMMENT 'Get Branches With Departments '
BEGIN
SELECT * FROM branches
WHERE branches.active = 1
AND branches.id <= branchId
ORDER BY branches.id;
END;
I got error :
SQL Error [1064] [42000]: 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 'END' at line 1
on compile :
https://img001.prntscr.com/file/img001/h-ZI1WTBRu-ZiEALdItKqw.png
What is wrong im my syntax ? Please examples how can I implement joining 2 tables Branches and Departments tables into 1 result set with fields like
id,
name,
parent_id - is null for Branches and not full for Departments
Thanks in advance!
When you do a select inside a procedure, you need to store the result in a variable, and then return it, you can do that using INTO, but your code would generate more than one result, that means that you can't use a procedure like that, and procedures are normally used to manipulate the data, not fetch everything from one or more tables.
@Tray2 Please give links to such examples, no prior expierence working on my sql side
I managed to write a procedure :
DELIMITER ;;
DROP PROCEDURE IF EXISTS getBranchesWithDepartments ;;
CREATE PROCEDURE `getBranchesWithDepartments` ( IN branchId smallint unsigned, OUT branch_id smallint unsigned, OUT branch_name varchar(100) )
SQL SECURITY DEFINER
BEGIN
SELECT branches.id, branches.name FROM branches
WHERE branches.active = 199
AND branches.id <= branchId
ORDER BY branches.id;
END;;
Which is saved.
But calling it
call getBranchesWithDepartments(1);
I got error :
Incorrect number of arguments for PROCEDURE getBranchesWithDepartments; expected 3, got 1
Which is valid syntax ?
CREATE PROCEDURE `getBranchesWithDepartments` (
IN branchId smallint unsigned, -- This is the 1 you passed.
OUT branch_id smallint unsigned, -- You need to pass this one as well
OUT branch_name varchar(100) ) -- And this one
You need to pass the OUT variables as well.
However, you need to explain why you want to use stored procedures in your database.
@Tray2 a) But in resulting data I need to get rows from result set, not 1 row. Will OUT work here or I need to return result set rows in other way ?
b) To use https://github.com/codewithdennis/filament-select-tree plugin I need to get data from 2 tables Branches and Departments tables into 1 result set with fields like
id,
name,
parent_id - is null for Branches and not full for Departments
c) In my procedure I just return data from 1 table for simplicity, but I need to extend its functionality to point b) above demand.
@PetroGromovo The question is still, why do you want to use store procedures?
They should not be used for just fetching data like you do, handle that in you php code instead,
Anyways this link migth prove useful
@Tray2 I do not know how pass custom data to https://github.com/codewithdennis/filament-select-tree plugin. Just as you wrote retrieve data with models and join data in a way I need. If there is such a way ? I think to make it with server's store procedure
@PetroGromovo Still don't know what the hell you are trying to do. There is no reason whatsoever to use a regular sql statement inside a stored procedure like that.
Write your query using Plain sql, the query builder, or eloquent inside your Filament Resource instead.
Please or to participate in this conversation.