Anyone familiar with prepared SQL?
I seem to have a problem with this. I think I may need quotes around something in the stored procedure but how would that be accomplished?
delimiter //
CREATE PROCEDURE listTasks(IN col CHAR(64), IN username CHAR(64), IN accesslevel CHAR(1), IN completed CHAR(1), IN duedate CHAR(10))
BEGIN
SET @select1 = "SELECT tv_cases.caseID,defFname,defLname,casenum,CASE juris WHEN 'dgd' THEN 'dg' WHEN 'dgj' THEN 'dg' ELSE juris END as juris";
SET @select2 = ",taskID,tasktype,owner,taskDate,delegated_to,dueDate,task,taskNotes,IF (comp=1,'Yes','No') comp,compDate";
SET @select3 = " FROM tv_cases, tv_tasks WHERE ";
SET @select4 = " AND tv_cases.caseID = tv_tasks.caseID AND ";
SET @select5 = " AND tv_tasks.comp = ";
SET @select6 = " AND DATE(dueDate) <= ";
SET @select7 = " ORDER BY dueDate";
SET @s = CONCAT(@select1, @select2, @select3, accesslevel, @select4, col, '=', username, @select5, completed, @select6, duedate, @select7);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
delimiter ;
-- call listTasks ('owner', 'frank', '1', '0', '2020-10-10')
-- #1054 - Unknown column 'frank' in 'where clause'