Error in calling stored procedure

Help Procedural SQL
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.

4

can you share me dynamic query, i am facing issue while executing dynamic query in memsql

5

create or replace procedure check_data(proc_date date) RETURNS decimal(13,4) as
declare
_adder_debit decimal(13,4);
begin
_adder_debit = SCALAR('SELECT SUM(ADDER_AMOUNT) FROM DELL_CR_ADDER_COST WHERE DATE = ‘|| proc_date || ’ AND SIGN(ADDER_AMOUNT)= -1 GROUP BY LEDGER’,QUERY(ADDER_AMOUNT decimal(13,4)));

RETURN _adder_debit;
EXCEPTION
WHEN OTHERS THEN
DECLARE

tmp text = exception_message();

BEGIN
INSERT INTO DELL_CR_LOG_MSG ( LOG_DATE ,PROC_NAME,ERROR_MESSAGE) values (NOW(),‘check_data’,_adder_debit);
RETURN _adder_debit;
END;
end; //

when i am doing ECHO on this proc its giving me null data , but actually table is having data.
can you help in checking if anything is wrong in this dynamic query.