Sql syntax error in procedure

Question

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 .

remove the erroneous line and Uncomment the same above line. — Ravinder Reddy
– Ravinder Reddy, Commented Jun 30, 2014 at 6:56
@Ravinder I removed the commented code and tried but still getting the same error — Nags
– Nags, Commented Jun 30, 2014 at 6:59
What is the output of select @sqlCalls;? place this statement before prepare and run. — Ravinder Reddy
– Ravinder Reddy, Commented Jun 30, 2014 at 7:22

Jens · Accepted Answer · 2014-06-30 11:10:46Z

1

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

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

edited Jun 30, 2014 at 11:10

answered Jun 30, 2014 at 6:55

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nags Over a year ago

Mean I have to do left join first then followed by where

Jens Over a year ago

@user1534577 Yes thats what i mean.

Nags Over a year ago

Please see the commented line after select statement in above procedure I tried that way getting the same error

Jens Over a year ago

@user1534577 you have a GROUP BY at the end of join. That must be after the WHERE statement.

Collectives™ on Stack Overflow

Sql syntax error in procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related