I am using stored procedure in creating table adapter. This is my syntax:
DELIMITER $$
DROP PROCEDURE IF EXISTS `lcs_rdb`.`sp_EmployeeListReport`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_EmployeeListReport`(in where_clause TEXT)
BEGIN
SET @sql = CONCAT("SELECT
emp.employee_id,
group_concat(distinct `ext`.`extension_number` separator ',') AS extension_number,
emp.employee_lastname, emp.employee_firstname,
dept.department_description,
divs.division_description,
grps.group_description,
st.site_name, emp.audit_date,
group_concat(distinct `cst_ctr`.`cost_center_description` separator ',') AS cost_center_description
FROM employees emp INNER JOIN
employee_extensions emp_ext ON emp_ext.employee_id = emp.employee_id INNER JOIN
extensions ext ON ext.extension_id = emp_ext.extension_id INNER JOIN
departments dept ON emp.department_id = dept.department_id INNER JOIN
divisions divs ON dept.division_id = divs.division_id INNER JOIN
groups grps ON divs.group_id = grps.group_id INNER JOIN
sites st ON emp.site_id = st.site_id LEFT OUTER JOIN
employee_cost_centers emp_cstctr ON emp.employee_id = emp_cstctr.employee_id LEFT OUTER JOIN
cost_centers cst_ctr ON emp_cstctr.cost_center_id = cst_ctr.cost_center_id
where ", where_clause ,"");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
This is the error when I executed it:
"Generated SELECT statement.
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 'NULL' at line 1"
I think, the problem is in the where clause. But I don't know how to fix it. Please help. Thanks.
CONCAT()
returns NULL
if any values are NULL
. Perhaps you want something like:
CONCAT('select . . .',
COALESCE(where_clause, '1 = 1'))