How to write dynamic query in stored procedure in mysql?

Raj

648 Points

26 Posts

I'm try to execute dynamic query in MySQL and I'm trying following in stored procedure:

CREATE PROCEDURE `GetMessagePagination`( 
    IN PageNumber INT(10) , 
    IN PageSize INT(10), 
    IN Sort VARCHAR(30) , 
    INOUT TotalCount INT(10) 
) 

BEGIN 

    SELECT * from messages ORDER BY Sort DESC LIMIT PageSize OFFSET PageNumber; 
    set TotalCount=(SELECT count(*) from messages); 

END

But getting error.

Views: 605
Total Answered: 2
Total Marked As Answer: 1
Posted On: 19-Sep-2022 00:16

Share:

Use prepare statement for dynamic query.

- Rashmi 19-Sep-2022 00:25

Answers

Priya

1194 Points

33 Posts

We can write dynamic query in MySQL by prepare statement like:

    SET @SQLText = CONCAT('SELECT * from messages ORDER BY', Sort, 'DESC LIMIT', PageSize, 'OFFSET', PageNumber);    
    PREPARE stmt FROM @SQLText;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Posted On: 19-Sep-2022 00:31

nome

30 Points

0 Posts

Try this one:

CREATE PROCEDURE `GetMessagePagination`( 
    IN PageNumber INT(10) , 
    IN PageSize INT(10), 
    IN Sort VARCHAR(30) , 
    INOUT TotalCount INT(10) 
) 

BEGIN 

    SET @SQLText = CONCAT('SELECT * from messages ORDER BY', Sort, 'DESC LIMIT', PageSize, 'OFFSET', PageNumber);    
    PREPARE stmt FROM @SQLText;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    set TotalCount=(SELECT count(*) from messages); 

END

Posted On: 19-Sep-2022 01:14

thanks.

- Raj 24-Sep-2022 01:02