How to write dynamic query in stored procedure in mysql?

Raj
Raj
Member
54 Points
17 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: 69
Total Answered: 2
Total Marked As Answer: 1
Posted On: 19-Sep-2022 00:16

Share:   fb twitter linkedin
Use prepare statement for dynamic query.
 - Rashmi  19-Sep-2022 00:25
Answers
Priya
Priya
Member
224 Points
52 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
nome
12 Points
1 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
 Log In to Chat