Error in calling stored procedure


#1

My database name is MIGSPROC and i have created the below procedure and compiled it successfully :

DELIMITER //
CREATE OR REPLACE PROCEDURE testproc2(_dept_name varchar(50)) AS
DECLARE
_emp_id int ;
_emp_name varchar(100);
_emp_city varchar(50);

qry2 QUERY(emp_id int, emp_name varchar(100), emp_city varchar(50)) = TO_QUERY('select empid, empname, city from my_MemSQL_table where department = '||_dept_name);
arr2 ARRAY(RECORD(emp_id int, emp_name varchar(100), emp_city varchar(50)));

BEGIN

arr2 = COLLECT(qry2);

FOR rec in arr2 LOOP
	_emp_id = rec.emp_id;
	ECHO SELECT _emp_id  AS value;
END LOOP;

END //

DELIMITER ;

But when i am calling it CALL testproc2(‘IT’);
i am getting the following error :

ERROR 1064 (42000): Unhandled exception
Type: ER_PARSE_ERROR
Message: 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 ‘0’ at line 1
Callstack:
#0 Line 7 in MIGSPROC.testproc2

What’s the issue??

I have tried to call it as CALL MIGSPROC.testproc2(‘IT’); , but it’s not working.


#2

You’re executing this dynamic query: 'select empid, empname, city from my_MemSQL_table where department = '||_dept_name

With your example parameter IT, the query would be: select empid, empname, city from my_MemSQL_table where department = IT

I think that you need to include escaped quotes in your dynamic query so that the filter is department = ‘IT’. Otherwise, it’s looking for a field called IT.

‘select empid, empname, city from my_MemSQL_table where department = ‘’’||_dept_name||’’’’ might work.

However, this may also assume that you have PIPES_AS_CONCAT enabled in your system. If not, then using the pipes (||) to concatenate will not work. Instead, you’d have to use the CONCAT function.


#3

Thanks @bvincent , actually PIPES_AS_CONCAT is not enabled in our system which is causing the problem, i have tried CONCAT function and it’s working awesome.