CODEWITHSUNDEEP
mysqlsqlprocedure
Nags
Nags

Reputation: 157

Sql syntax error in procedure

I am writing one procedure in order to get data from database .

Here is my procedure .

CREATE DEFINER=`root`@`localhost` PROCEDURE `getCdrListnew`(from_date DATETIME, to_date DATETIME, 
 p_category VARCHAR(255), p_callType VARCHAR(255), p_businessId VARCHAR(255), p_dnc VARCHAR(255),
 p_cli VARCHAR(255), p_dni VARCHAR(255), p_callNumber VARCHAR(255), p_dialType VARCHAR(255), p_contractType VARCHAR(255) )
BEGIN
       DECLARE a INT ;
       DECLARE setFilter INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
       SET a = 0;
     BEGIN
END;


    SET @sqlCalls = 'SELECT agentcdr.isdnCauseCode,COUNT(*) as count  FROM cdr';  
 -- SET @sqlCalls= CONCAT(@sqlCalls,' LEFT JOIN agentcdr ON cdr.idCDR=agentcdr.cdr_idCDR ');


        IF( (from_date IS NOT NULL AND from_date != '')&& (to_date IS NOT NULL  AND to_date != '') ) THEN
   SET @sqlCalls= CONCAT(@sqlCalls,' WHERE cdr.createdDt >=\'',from_date,'\' AND cdr.createdDT<=', '\'',to_date,'\'');
   SET setFilter = TRUE;
  END IF; 

  SET @sqlCalls= CONCAT(@sqlCalls,' LEFT JOIN agentcdr ON cdr.idCDR=agentcdr.cdr_idCDR GROUP BY agentcdr.isdnCauseCode ');
  PREPARE prepsqlstr1 FROM @sqlCalls;
  EXECUTE prepsqlstr1;
END

When I am executing the above code I am getting an error as below .

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN agentcdr ON cdr.idCDR=agentcdr.cdr_idCDR GROUP BY agentcdr.isdnCauseCo' at line 1

I know this is the syntax problem but not able to figure out what I am doing wrong here .

Please help me out .

ALso do let me know if there any way to print the query which is executing in procedure for debugging purpose .

Upvotes: 1

Views: 68

Answers (1)

Jens
Jens

Reputation: 69450

Looks like you have a wrong order in the generated statement:

You get a select ... where ... left join .... This is wrong syntax.

Upvotes: 1

Related Questions