Dev Research Dev Research - 1 month ago 6
MySQL Question

how to pass value in dynamic query in MySQL inside procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `EventList_SP`(
in employeeId varchar(45),
in groupIdArray text,
in skillIdArray text,
in startDate date,
in endDate date
)
SET @empID = employeeId;

set @SQLQuery =CONCAT( "SELECT groupId,eventId,scheduleId,description,events,eventType,scheduledDate,name,designation,image,skills,duration,status,
CASE
WHEN
scheduledDate < NOW()
AND (SELECT
COUNT(*)
FROM
event_request
WHERE
event_id = eventId
AND employee_code =",@empID,") > 0
THEN
1
WHEN
scheduledDate < NOW()
AND (SELECT
COUNT(*)
FROM
event_request
WHERE
event_id = eventId
AND employee_code =",@empID,") = 0
THEN
0
ELSE ''
END AS hasRequested,
(SELECT
actual_attendance_status_id
FROM
TJU.event_attendees_mapping
WHERE
scheduleId = event_schedule_id
AND employee_code =",@empID,") AS attendingStatus,
meetingRoom
FROM
EventList_View");
PREPARE stmt FROM @SQLQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END


call EventList_SP('TJU_741','','2,19',


'2016-09-30 10:30:00','2016-10-19 10:30:00')

when i call using like this then i am getting Error Error Code:



  1. Unknown column 'TJU_741' in 'where clause'




while i want to set Value

please tell me how to pass value in dynmic query i have to pass value
TJU_741
to
where
clasue
employee_code =",@empID,"
but i am getting Error please tell me where am doing wrong.

Answer

employee_code =",@empID," (x3)

So if you pass a VARCHAR, result will be employee_code =TJU_ID which is a column. Either use ' (quotes) like employee_code = '",@empID,"' or, way more secure (but I don't know the how to here, see MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized ), use a prepared statement.

Comments