GROUP CONCAT is failing when Alphanumeric value is coming MYSQL stored procedure-CodePudding

I have sample procedure where I'm passing DB names as input param and it was looping fine and updating the statements. Please find the Below one :

DELIMITER &&  
    CREATE PROCEDURE update_stmt (IN DBName varchar(100))  
    BEGIN 

DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;
DECLARE schemaname varchar(64);

iterator:
LOOP

  IF CHAR_LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
    LEAVE iterator;
  END IF;
 
  SET _next = SUBSTRING_INDEX(_list,',',1);
  SET _nextlen = CHAR_LENGTH(_next);
  SET _value = TRIM(_next);
  SET schemaname = _value;

   SELECT concat('update ',schemaname, '.SAMPLE SET COL = 0 where ID IN (',GROUP_CONCAT(TICKET),');') AS statement
 FROM Test.SAMPLE

 INTO @sql; 
 PREPARE stmt from @sql;
 EXECUTE stmt;
DEALLOCATE PREPARE stmt;


  SET _list = INSERT(_list,1,_nextlen   1,'');
END LOOP;
END

When the Procedure executes it will create statement like the below if I print:

Update Test.sample SET COL = 0 WHERE ID IN (1,2,3)

For Example If I have ID Column like this in the table :

    **ID**
    1A
    2B
    3C

When I execute the procedure I expect to get the statement like below :

Update Test.sample SET COL = 0 WHERE ID IN ('1A','2B','3C')

But I'm getting error as

Unnkown column 1A in where clause

In the Groupconcat how I need to handle alphanumeric values and one more thing FROM Test.SAMPLE.

I'm passing variable in the place of DB Name it is giving error as schemaname.SAMPLE doesn't exist.

Please suggest

CodePudding user response：

Provided ID column is a char string, add single quotes around the TICKET value .. GROUP_CONCAT(CONCAT('''', TICKET, '''')) .. Also check if @sql is null (Test.SAMPLE is empty)

   ..
   if @sql is not null then
     PREPARE stmt from @sql;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
   end if;

To stop the proc gracefully at error provide an exit handler for SQLEXCEPTION. For example

CREATE PROCEDURE update_stmt (IN DBName varchar(100))  
BEGIN 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    /* Your error processing here */ 
  END;
  ..

db-fiddle