Stored procedure mysql syntax

Question

Please help me to solve the mistake done in this stored procedure:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `repos`(IN `order_condition` VARCHAR(100) CHARSET utf8, IN `from_date` VARCHAR(255) CHARSET utf8, IN `to_date` VARCHAR(255) CHARSET utf8, IN `dept_id` TINYINT(4), IN `type` VARCHAR(15) CHARSET utf8, IN `search_value` VARCHAR(50) CHARSET utf8, IN `institutionid` INT(11))
    NO SQL
BEGIN

set @strWhere   = "";
set @strWhere   = CONCAT(@strWhere,"t1.inst_id = ",instid);
set @selectstr  = '';
set @selectstr2 = '';
set @joinstr    = '';
set @joinstr2   = '';
set @group2= '';

set @selectstr = CONCAT(",t2.`std_name`");
set @selectstr2= CONCAT(",t5.`student_name`,t5.`roll_no`,t5.`student_id`");
set @joinstr = CONCAT("left join standard_master as t2 on t1.`std_id`= t2.`std_id` ");
set @joinstr2 = CONCAT("left join student_det as t5 on t1.`std_id`= t5.`std_id` ");
set @strWhere = CONCAT(@strWhere," AND t1.std_id = ",dept_id);
set @group2= CONCAT("t5.`student_id`");


SET @query = CONCAT("select t1.* ",@selectstr," , t3.term_name, SUM(t4.amount) as fee_amount",@selectstr2," FROM inst_fee AS t1 ",@joinstr," left join term_master as t3 on t1.term_id = t3.term_id left join inst_amtdet as t4 on t4.masterid = t1.masterid ",@joinstr2,"  WHERE ",@strWhere,  " AND ",@group2,  " NOT IN (select id from student_master) Group by t1.f_name  ", order_condition);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#select @query;


END

It shows a syntax error:

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 'IN(

Please help me to fix the error. Thanks

@Devart Yes, but it doesn't have a closing parenthesis. When MySQL reports syntax error it always includes a literal quote of the offending code so this is either not the real message or it's triggered by different code. — Álvaro González, Feb 21 '17 at 11:44

Devart · Answer 1 · 2017-02-21T11:30:32.820

0

The syntax is correct. It works on new MySQL servers.

It is possible that you use old MySQL server, where CHARSET clause was not supported for string parameters. Check the syntax for your server version.

edited Feb 21 '17 at 11:30

answered Feb 21 '17 at 11:21

Devart

119,203
23
166
186

Thanks.Ya i have used the delimiter when i try to save i am getting the error.cant able to save – User11 Feb 21 '17 at 11:33
I have changed answer. Check it. What is the server version? – Devart Feb 21 '17 at 11:34
Ya checcked Its 5.6.21 – User11 Feb 21 '17 at 11:42
Ya I tried but on saving getting error as ,MySQL said: #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 '$$ Pls help me to find the solution. – User11 Feb 21 '17 at 12:02
What client do you use? – Devart Feb 21 '17 at 12:05
I use Phpmyadmin to access database – User11 Feb 21 '17 at 12:15
DELIMITER is a client command. As I know, it is not possible to use this comamnd in phpmyadmin. There is an option (workaround) for phpmyadmin - http://stackoverflow.com/questions/16801550/creating-functions-in-phpmyadmin-error-access-denied-you-need-the-super-privi/16802061#16802061 – Devart Feb 21 '17 at 12:26
Or, if it is possible to access the server from your host, you can use mysql command line client, or our free tool dbForge Studio (Express) to run such scripts. – Devart Feb 21 '17 at 12:28
Ya i have already tried the link given above still i dont get any rectification – User11 Feb 22 '17 at 04:31
It seems that problem with procedure execution, not creating. Possible - problem with constructed query inside the procedure. – Devart Feb 22 '17 at 06:48
Also, unknown value `order_condition`. – Devart Feb 22 '17 at 06:49

Stored procedure mysql syntax

1 Answers1