0

Please help me.. I am declaring cursor with IN clause value getting from other table where the value will be with comma (,) seprated and storing that into a variable and giving to the IN clause.

Here is my code:

CREATE  DEFINER=`xxx`@`%`  PROCEDURE `procedurename`(
OUT examDetails VARCHAR(2500)
)
BEGIN

DECLARE v_examType VARCHAR(50);
DECLARE v_minMarks INT(10);
DECLARE v_time INT(10);
DECLARE v_noOfQuestions INT(10);
DECLARE v_subTopicIds VARCHAR(50);
DECLARE v_subtopicname VARCHAR(50);
DECLARE v_result VARCHAR(2500) DEFAULT '';
DECLARE v_temp INT(20);



DECLARE cur1 CURSOR FOR SELECT ExamType,MinMarks,TIME,NoOfQuestions,SubTopicIds FROM tblCreSetExam;


 select ExamType,MinMarks,Time,NoOfQuestions,SubTopicIds from tblCreSetExam
OPEN cur1;
    FETCH FROM cur1 INTO v_examType,v_minMarks,v_time,v_noOfQuestions,v_subTopicIds;

     declare cur2 cursor for select SubTopicName from tblEcertSubTopics where Id in (v_subTopicIds);




    v_result = CONCAT(v_result,v_examType,'!',v_minMarks,'!',v_time,'!',v_noOfQuestions,'!');
    open cur2;
        fetch from cur2 into v_subtopicname;

        v_result = CONCAT(v_result,v_subtopicname,'!');

     close cur2;
        v_temp = LENGTH(v_result)-1;
        v_result = SUBSTRING(v_result,1,v_temp);
        v_result = CONCAT(v_result,'^')


CLOSE cur1;



SET examDetails = v_result;

END$$

DELIMITER ;

But i am getting this error:

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your
MySQLserver version for the right syntax to use near 'declare cur2 cursor for select
column1 from table1 where Id in (' at line 20

do any one know why this is getting.. Thanks in advance

showdev
  • 25,529
  • 35
  • 47
  • 67
Aj.
  • 27
  • 1
  • 7
  • 1
    You appear to have a random select in the middle of your code, which is also missing a ; from the end. As to the IN clause, you appear to be using an IN with only a single item in the brackets. Might be better to just use a JOIN. – Kickstart Feb 07 '14 at 10:18

2 Answers2

0

You cannot use an IN CLAUSE with a VARCHAR variable.

This will not work: 

where Id in (v_subTopicIds)

Perhaps do what you are trying in code instead of a SQL procedure, otherwise you need to rethink your strategy

Brendan
  • 1,217
  • 1
  • 15
  • 34
  • OK how can i cast from varchar to int and store in a variable and then i can give that to my query ?? – Aj. Feb 07 '14 at 10:30
  • Have a look at: http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function – Brendan Feb 07 '14 at 10:48
0

There exist multiple errors in your code.

Error 1: 

FETCH FROM cur1 INTO v_examType,v_minMarks,v_time,v_noOfQuestions,v_subTopicIds;

Change it to: 

FETCH cur1 INTO v_examType,v_minMarks,v_time,v_noOfQuestions,v_subTopicIds;

Error was due to presence of FROM. It was not required.

Error 2:

Change 

v_result = CONCAT(v_result,'^')

To: 

v_result = CONCAT(v_result,'^');

Semi-colon is missing.

Ravinder Reddy
  • 22,363
  • 6
  • 46
  • 76
  • modified my procedure please check again and i am getting error as Error Code : 1064 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 'close cur1; set examDetails = v_result; END' at line 47 – Aj. Feb 07 '14 at 10:36
  • removed from but not effected Please help – Aj. Feb 07 '14 at 10:38
  • append semi-colon next to `v_result = CONCAT(v_result,'^')`. It is missing. – Ravinder Reddy Feb 07 '14 at 10:38
  • Ravinder : how can i cast from varchar to int and store in a variable and then give that to a query..please help – Aj. Feb 07 '14 at 10:42
  • I believe this is for your `in` clause but the query was not that clear. post new question with valid input and required result values. – Ravinder Reddy Feb 07 '14 at 10:47
  • called procedure CALL procedurename(@result); Query : call spGetCreExamDetails(@result) Error Code : 1054 Unknown column 'v_subTopicsIds' in 'field list' Pleas help me – Aj. Feb 07 '14 at 10:48