Passing multiple value for a parameter in my sql I have written the query below:

SET @City = 'London,Lyon,Kln';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@City, ',',@r:=@r+1),',',-1) City
FROM (SELECT @r:=0) x,
(SELECT 'x' xx 
    UNION 
    SELECT 'v' xx
    UNION
    SELECT 'z' xx) z;


Which returns Cities in table format &amp; now i want to pass it as a parameter to a query like :

SET @City = 'London,Lyon,Kln';
SELECT * 
FROM Invoices 
WHERE City IN (
    SELECT SUBSTRING_INDEX(
        SUBSTRING_INDEX( @City, ',',@r:=@r+1),',',-1
    ) City
    FROM (SELECT @r:=0) x,
    (SELECT 'x' xx 
        UNION
        SELECT 'v' xx 
        UNION
        SELECT 'z' xx) z
)


But when I execute this query it returns the records for 'Kln' city only, ie. for last city mentioned in @City variable. So please tell me how to get the records for all cities of @City variable.
If I understand your intent, I might write the query something like this:

SET @City = '"London","Lyon","Kln"';
SET @sql = concat('SELECT * FROM Invoices WHERE City IN (',@City,')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;