0

I have the following code in my stored procedure.

PREPARE seedrecordfetchStatement FROM 
    "CREATE TEMPORARY TABLE seedrecordfetch 
      ENGINE = MEMORY 
        SELECT s.id, i.thumbnailImageUrl, s.daysUntilHarvest, 
                s.minimumGerminationTemperature, s.plantHeight, s.color 
        FROM seedrecord s 
            LEFT JOIN image i ON s.image_id = i.id 
        WHERE s.id IN ( ? ) 
        AND s.deleted = false";

the value for ? is a commaseperated string of ids of seedrecord rows of which i know they are present in the SeedRecord table. Yet, no matter how many ids i include the temporary table seedrecordfetch is always created with one row only. That row always corresponds with the first id in the comma separated id string.

Running the select query separatly returns all the ids that are in the list, so the problem is not with the SELECT statement.

Am i forgetting something here? Or is it simply not possible to create a temporary table with multiple rows of data?

Thank you

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Maurice
  • 6,698
  • 9
  • 47
  • 104
  • Parameter substitution is a single value, it doesn't expand the string into multiple elements of the list. – Barmar Mar 26 '20 at 20:21
  • Use `WHERE FIND_IN_SET(s.id, ?)` – Barmar Mar 26 '20 at 20:21
  • you're the best @Barmar :) Thanks for helping me out so fast! I'm still a novice when it comes to stored procedures and i wasn't aware that i should use FIND_IN_SET instead. Please submit this as an answer and I will accept it. Thank you – Maurice Mar 26 '20 at 20:24

0 Answers0