2

I have a select query that uses a WHERE IN clause in a MemSQL database. The list of values searched in are a result of another select query.

Before, I inserted the values for that clause as a string through a web service like so: (1,2,3..).

Now I want to move the second query into a stored procedure, and have the values mentioned above be a part of the parameters for that procedure.

Say I have a table:

create table t (num int);

Then when I try to create the sp:

DELIMITER //
CREATE PROCEDURE test (nums ARRAY(INT)) AS 
BEGIN
ECHO SELECT * FROM t WHERE num IN nums;
END //
DELIMITER ;

I get:

ERROR 1064 (42000): Compilation error in function `test` near line 3: 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 'nums'

So apparently it doesn't work that way. Is there way I can get this to recognize the data type I'm inserting? I want to avoid manipulating strings, but if there is no choice, would it require running the query as a string in the stored procedure?

1 Answer 1

1

Try adding nums in brackets so it can complete the required syntax.

DELIMITER //

CREATE PROCEDURE test (nums ARRAY(INT)) 
AS 
BEGIN
ECHO SELECT * FROM t WHERE num 
IN(nums);
END //
DELIMITER ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.