Dev Research Dev Research - 1 year ago 109
MySQL Question

how to count total record using Dynamic query in mysql

CREATE DEFINER=`root`@`localhost` PROCEDURE `EventList_SP`(
in employeeId varchar(45),
in eventTypevalue int,
in groupIdArray text,
in searchTermtext text,
in skillIdArray text,
in startDate date,
in endDate date,
in offsetvalue int,
out total int

SET @empID = employeeId;
SET @Whereclause='where 1=1';

if(groupIdArray is not null) then
set @Whereclause=CONCAT(@Whereclause," and FIND_IN_SET(groupId,'",groupIdArray,"')" );
end if;

if(skillIdArray is not null) then
set @Whereclause=CONCAT(@Whereclause," and FIND_IN_SET(groupId,'",skillIdArray,"')" );
end if;
if(startDate is not null and endDate is not null) then
set @Whereclause=CONCAT(@Whereclause," and (scheduledDate between
'",startDate,"' and '",endDate,"')" );
end if;
if(eventTypevalue is not null and eventTypevalue=0) then
set @Whereclause=CONCAT(@Whereclause," and scheduledDate<'",curdate(),"'" );
end if;

if(eventTypevalue is not null and eventTypevalue=1) then
set @Whereclause=CONCAT(@Whereclause," and scheduledDate>'",curdate(),"'" );
end if;

if(searchTermtext is not null ) then

set @Whereclause=CONCAT(@Whereclause," and searchTerm like'",searchTermtext,"'" );
end if;

set @SQLQuery =CONCAT("select groupId,eventId,scheduleId,description,events,eventType,
case when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code='TJU_741')>0 then 1
when scheduledDate < now() and (select count(*) from event_request where
event_id=eventId and employee_code='TJU_741')=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='TJU_741')
as attendingStatus,
meetingRoom from EventList_View ", @Whereclause );

/* set @SQLQuery=CONCAT("select count(*) from EventList_View",@Whereclause,"into",total) ;


this is my Procedure i am able to get Data when pass different parameter i want to get total return row count and i have to set as output parameter but i am getting null when i use my query for count number record which i have comment please suggest me how to get total number of record using dynamic query

Answer Source

If I understand correctly, you would set things up like this:

set @sql = 'select SQL_CALC_FOUND_ROWS . . . ';
set @total = -1;
prepare @sql;
execute @sql;

execute 'select found_rows() into @total' ;

set out_total = @total;

Note: I changed the parameter name to out_total. You should distinguish parameter names from column names using some sort of naming convention.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download