How to write dynamic query in stored procedure in mysql?

Raj

Member

496 Points

21 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: 536
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

Participant

936 Points

28 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

Member

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