function to eliminate columns fails (MYSQL)

Question

function to eliminate columns fails (MYSQL)

Navigation

#1 by (3 votes)

1

Hello, how are you? I have a mysql function that at one moment worked, and now for no apparent reason fails, and I always throw the same error, here 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 do not understand why, I see everything correctly, I hope you can help me. Thanks.

mysql

asked by Bernardo Harreguy 20.12.2017 в 21:09

source

1 answer

How to modify the Sqlite Path Sort by Mysql

score 3 · Accepted Answer

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

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

When using that type of quotes, MySQL thinks that you are referring to some column of a table. To pass text strings, you must use the single quotes, like this:

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