CODEWITHSUNDEEP
mysql
Dev Research
Dev Research

Reputation: 61

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.

Upvotes: 0

Views: 1953

Answers (1)

Xenos
Xenos

Reputation: 3507

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.

Upvotes: 2

Related Questions