Home / Questions / 126199
Accepted
Bernardo Harreguy
Asked: 2020-12-21 12:09:26 +0800 CST 2020-12-21 12:09:26 +0800 CST

function to delete columns fails (MYSQL)

  • 772

Hi how are things? I have a mysql function that worked at one point, and now for no apparent reason it fails, and always throws me the same error, here is the code

CREATE DEFINER=`root`@`localhost` PROCEDURE `borrar_columna`(
    IN `nombredecolumna` TEXT ,
    IN `nombredetabla` TEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF (SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = nombredetabla AND table_schema = DATABASE()) = 1 THEN
SET @sqlstmt = CONCAT('DROP TABLE `', nombredetabla ,'`;');
PREPARE st FROM @sqlstmt;
EXECUTE st;
DEALLOCATE PREPARE st;
ELSE
IF (SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = nombredecolumna AND table_name = nombredetabla AND table_schema = DATABASE()) = 1 THEN
SET @sqlstmt = CONCAT('ALTER TABLE `', nombredetabla ,'` DROP COLUMN `', nombredecolumna ,'`;');
PREPARE st FROM @sqlstmt;
EXECUTE st;
DEALLOCATE PREPARE st;
END IF;
END IF;
END

The error I get is the following...

SQL error (1054) column '123' in field list is unknown

the command I give is like this

CALL borrar_columna(`123`,`prueba`);

Of course the column and the table exist and are created to do the test. It worked several times, but now it fails, I don't understand why, I see everything correctly, I hope you can help me. Thanks.

mysql

1 Answers

  1. Best Answer

    The problem is the type of quotes you use in the call:

    CALL borrar_columna(`123`,`prueba`);

    By using such quotes, MySQL thinks that you are referring to some column of some table. To pass text strings to it, you must use single quotes, like this:

    CALL borrar_columna('123','prueba');
    • 3