How to build dynamic query into this procedure

Question

I am trying to build a dynamic query with a stored procedure in mysql database but still see this error. Why?

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 7

DROP PROCEDURE IF EXISTS mp_test;
CREATE PROCEDURE mp_test(
    IN PrStatus VARCHAR(255),
    IN PrStatusCode VARCHAR(255),
    IN PrProviderId VARCHAR(255),
    IN PrRow VARCHAR(255))
   BEGIN
      SET @Condition  = '';
      SET @Query = '   
      SELECT
      bitauth_users.fullname,
      tbservices.ServiceNameAR,
      tbservices.ServiceNameEN,
      tbservices.ServiceId,
      globrx.ProviderId,
      globrx.RequestDesc,
      TbServicesRequestsLog.status
        FROM tbservices, Tbservicesrequests globrx, 
        TbServicesRequestsLog, bitauth_users  
        WHERE  globrx.ProviderId = 'Prproviderid' ';

      IF PrStatus != '' THEN
         SET @Condition = CONCAT(' AND globrx.RequestId = TbServicesRequestsLog.RequestId');
         SET @Condition = CONCAT(' AND globrx.ServiceId = tbservices.ServiceId');
         SET @Condition = CONCAT(' AND bitauth_users.user_id = globrx.ReceiverId');
         SET @Condition = CONCAT(' AND globrx.RequestId = TbServicesRequestsLog.RequestId');
         SET @Condition = CONCAT('AND (SELECT status  FROM TbServicesRequestsLog,Tbservicesrequests AS subrx
                                  WHERE subrx.RequestId = TbServicesRequestsLog.RequestId 
                                  AND subrx.ServiceId = globrx.ServiceId
                                  ORDER BY RequestLogId DESC  LIMIT 1)"', status_code, '"');
         SET @Condition = CONCAT('GROUP BY TbServices.ServiceId ORDER BY TbServices.ServiceId ASC LIMIT 'PrRow',10')
               
                                  
      END IF;
      
      SET @Query = CONCAT(@Query, @Condition);

 PREPARE stmt FROM @Query;
 
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;  
END 

Answer 1

I don't know what do your stored procedure... but the fixed version is: It have several errors:

1. Must set a delimiter as default, because the body will contain ; , the first line I changed the delimiter to $$

2. The concatenation is not automatic:

   WHERE  globrx.ProviderId = 'Prproviderid' '; 
   

   Must change to

   concat('...
   WHERE  globrx.ProviderId = ',Prproviderid,' '); 
   

3. The @condition is a cumulative variable (I think) :

   SET @Condition = CONCAT(@Condition,
   

There is the result:

DELIMITER $$
DROP PROCEDURE IF EXISTS mp_test$$
CREATE PROCEDURE mp_test(
    IN PrStatus VARCHAR(255),
    IN PrStatusCode VARCHAR(255),
    IN PrProviderId VARCHAR(255),
    IN PrRow VARCHAR(255))
   BEGIN
      SET @Condition  = '';
      SET @Query = concat('   
      SELECT
      bitauth_users.fullname,
      tbservices.ServiceNameAR,
      tbservices.ServiceNameEN,
      tbservices.ServiceId,
      globrx.ProviderId,
      globrx.RequestDesc,
      TbServicesRequestsLog.status
        FROM tbservices, Tbservicesrequests globrx, 
        TbServicesRequestsLog, bitauth_users  
        WHERE  globrx.ProviderId = ',Prproviderid,' ');

    SET @Condition = '';

      IF PrStatus != '' THEN
         SET @Condition = CONCAT(@Condition,' AND globrx.RequestId = TbServicesRequestsLog.RequestId');
         SET @Condition = CONCAT(@Condition,' AND globrx.ServiceId = tbservices.ServiceId');
         SET @Condition = CONCAT(@Condition,' AND bitauth_users.user_id = globrx.ReceiverId');
         SET @Condition = CONCAT(@Condition,' AND globrx.RequestId = TbServicesRequestsLog.RequestId');
         SET @Condition = CONCAT(@Condition,' AND (SELECT status  FROM TbServicesRequestsLog,Tbservicesrequests AS subrx
                                  WHERE subrx.RequestId = TbServicesRequestsLog.RequestId 
                                  AND subrx.ServiceId = globrx.ServiceId
                                  ORDER BY RequestLogId DESC  LIMIT 1) "', status_code, '"');
         SET @Condition = CONCAT(@Condition,' GROUP BY TbServices.ServiceId ORDER BY TbServices.ServiceId ASC LIMIT ',PrRow,',10');


      END IF;

      SET @Query = CONCAT(@Query, @Condition);

    PREPARE stmt FROM @Query;

    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;  
END $$