Mysql build query dynamically with placeholders using Limit , How to create this in store procedure

Question

I want to implement custom paging in my store procedure there for I used limit, It works fine when I execute query in MY SQL as static

sample select query

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
WHERE FID.FlightInstanceId > 6
GROUP BY FID.FlightInstanceId 
LIMIT 1, 5;

But now I want dynamically for pagging using perameters therefore I used as

SET @skip=skip; SET @numrows=numrows;
SET @ID = ID;
PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > 6
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

still working and getting data

enter image description here

when I create this query as store procedure then it is execute successfully but it didn't gives any result

DELIMITER $$

USE `mydatabase`$$

DROP PROCEDURE IF EXISTS `SP_Temp`$$

CREATE DEFINER=`mydatabase`@`%` PROCEDURE `SP_Temp`(
    skip INT,
    numrows INT,
    ID INT
)
BEGIN
DECLARE skip INT;
DECLARE numrows INT;
DECLARE ID INT;
SET @skip=skip; SET @numrows=numrows;
SET @ID = ID;
PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > @Id
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

END$$

DELIMITER ;

calling

CALL SP_Temp(1,5,40)

My question is when I calling this store procedure then it gives no result.where I am gone wrong ? I want to use Store procedure.

krokodilko · Accepted Answer · 2014-02-21 14:33:33Z

2

@Id must also use a placeholder.

Try:

PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > ?
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @Id, @skip, @numrows;

In the above query I replaced this line:

where FID.FlightInstanceId > @Id

with this one:

where FID.FlightInstanceId > ?

answered Feb 21, 2014 at 14:33

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MSTdev Over a year ago

Thx kordirko it is working , but still I need change in query for custom pegging. because limit '@skip' , '@numrows' , @skip :skip only one it is not use like page

krokodilko Over a year ago

LIMIT x, y measn "skip x rows, then return y next rows". If the page has Z rows, you must calculate yourself how many rows must be skipped using @skip = Z * (pageNo-1) formula.

MSTdev · Accepted Answer · 2014-02-21 16:18:58Z

Build query dynamically and Custom paging with Limit() is clear now and working.

DELIMITER $$

USE `mydatabase`$$

DROP PROCEDURE IF EXISTS `SP_Temp`$$

CREATE DEFINER=`mydatabase`@`%` PROCEDURE `SP_Temp`(
    skip INT, -- page number 
    numrows INT, -- page index 
    ID INT -- any of your where condition parameter 
)
BEGIN
DECLARE skip INT;
DECLARE numrows INT;
DECLARE ID INT;
SET @skip= (skip * numrows) - numrows  ; 
SET @numrows=numrows;
SET @ID = ID;
PREPARE STMT FROM '
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( (TIMEDIFF(FIS.ArrivalTime,FIS.DepartTime) ))) )AS duration,FID.FlightInstanceId,FID.FlightScheduleDate FROM FlightInstanceDetails AS FID
LEFT JOIN FlightInstanceSegmentAdjuster AS  FIS ON FIS.FlightInstanceId = FID.FlightInstanceId
where FID.FlightInstanceId > ?
GROUP BY FID.FlightInstanceId 
LIMIT ?, ?';
EXECUTE STMT USING @Id, @skip, @numrows;

END$$

DELIMITER ;

Collectives™ on Stack Overflow

Mysql build query dynamically with placeholders using Limit , How to create this in store procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related