1

I copied the code from here and made minimal changes

DELIMITER $$  
CREATE PROCEDURE updateTable (IN list_of_ids VARCHAR(32))  
BEGIN  
    set @sql = concat("SELECT (" , list_of_ids, ")");

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END$$

call updateTable('1,2,3');

But when I call I get Error 1241: Operand should have one 1 column(s)

I've used " instead of ' around the parameter and it still gave the same error. I also tried to declare a variable with the "1,2,3" string and it still failed, and I tried to call updateTable(concat("1,","2,","3")); to no avail.

I also tried to change the procedure to expect 3 parameters and I get the expected error Error 1318: Incorrect number of arguments

This is all being done in MySql if anyone knows what I'm missing, thanks! I've been googling for an answer all night.

Mendess
  • 69
  • 8

1 Answers1

0
SET @sql = CONCAT("SELECT (" , list_of_ids, ")");

why do you need those inner brackets in this line of code? That's the point where you have the issue. But If you want those numbers as a string in the procedure itself then the code is correct but you have passed values in wrong way. It should be passed as follows.

CALL updateTable('\'1,2,3\''); OR
CALL updateTable("'1,2,3'");

Let me know if there is any problem?

The example is correct, there it has update statement but you have a select statement. The difference is your code results as:

select (1,2,3)

In this case significance of '(' is out of scope.

and your example will be like 

update table1 SET ..... where id in (1,2,3);

which is correct.

krishna aryal
  • 514
  • 2
  • 9
  • Thanks for the clarification but now I'm lost in what I wanted to achieve. This code now produces a table with one column "'1','2','3'" and one line "'1','2','3'". And I wanted a table with one column "Name" and 3 lines. I want to insert multiple values into a table and I wanted to get those from a parameter. Therefore I need to create a table on runtime with one column and the values from the "array" in each line of the table. EDIT: I can't make tables in comments. – Mendess Nov 25 '17 at 12:59
  • Means you need data in three rows with one column ie. values with 1 2 and 3 in separate rows? or you want to select the result based on the passed ids? – krishna aryal Nov 25 '17 at 13:19
  • I want data in one column and three rows. – Mendess Nov 25 '17 at 13:21
  • Are you going to select data in from different table or just you want the values you passed in separate rows? – krishna aryal Nov 25 '17 at 13:26
  • Thanks for answering but I changed my google search and found [this](https://stackoverflow.com/questions/35169922/mysql-stored-procedure-insert-multiple-rows-from-list) which solved my problem. – Mendess Nov 25 '17 at 14:55