WEHRE IN array values using stored procedure

2 votes

1 answer

901 views

                          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? 
                        

Asked by Nexaspx (123 rep)

Sep 11, 2019, 07:19 AM
Last activity: Jul 26, 2022, 10:30 PM

WEHRE IN array values using stored procedure

Related Questions