I have built a stored procedure to return the list of Columns for a given Table and Database in JSON format.
However, it also returns columns from other tables despite the WHERE clause – This makes no sense, and I am unsure what I am doing wrong. Your help is most appreciated.
USE `db-xyz`; DROP PROCEDURE IF EXISTS GetColumnsInfo; DELIMITER $$ CREATE DEFINER = 'admin'@'localhost' PROCEDURE GetColumnsInfo (IN table_name varchar(128),OUT columns_info json) BEGIN SELECT JSON_OBJECTAGG( COLUMN_NAME, JSON_OBJECT( "column_name", COLUMN_NAME, "data_type", DATA_TYPE ) ) AS columns_info INTO @columns_info FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = table_name; SET columns_info = @columns_info; END $$ DELIMITER ;
This is how I am testing it:
USE `db-xyz`; SET @table_name = 'Client'; SET @columns_info = ''; CALL GetColumnsInfo(@table_name, @columns_info); -- Display the output SELECT @columns_info AS columns_info;
You should name your procedure parameters something different from your column names. Column names are case-insensitive.
Right now your procedure has a query with this condition:
... AND TABLE_NAME = table_name;
Both of these operands reference the column of the table, not the procedure parameter. MySQL cannot tell that you meant the latter operand to be the procedure parameter.
So this evaluates as
true because comparing a column to itself is bound to be true (unless the column is NULL).