2

How can I use a function argument s to stand in for a column name in a query? I'm using mysql 5.5. I tried

SELECT count_distinct(last_name)

but it returns

ERROR 1054 (42S22): Unknown column 'last_name' in 'field list'

The function was defined as

CREATE FUNCTION count_distinct (s CHAR(20) )
RETURNS INT
READS SQL DATA
RETURN( SELECT COUNT(DISTINCT s) FROM president);

I expected it to return a value of 37 as

SELECT COUNT(DISTINCT last_name) FROM president

returns 37. The 'president' table is from the sampdb database from MySQL by Paul Dubois.

1
  • 2
    You'll need to use dynamic SQL. Feb 13 '15 at 13:20
1

With thanks to Colin 't Hart this worked:

DELIMITER $
CREATE PROCEDURE showUnique( IN col CHAR(64))
BEGIN
SET @s=CONCAT('SELECT COUNT(DISTINCT ',col,') FROM president);
PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $
delimiter ;
CALL showUnique(last_name)
2
  • It works, but it shouldn't be done. Objects aren't data and data isn't objects. Crossing that boundary reeks of bad design and introduces potential security vulnerabilities. Try running CALL showUnique('1) UNION ALL SELECT user FROM mysql.user); #');, for example. Feb 13 '15 at 21:29
  • Thanks. How should it be done, then?
    – oks
    Feb 13 '15 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.