Variable collation with MySQL stored function?
- by Chad Johnson
I want to do something like this in a stored procedure:
IF case_sensitive = FALSE THEN
SET search_collation = "utf8_unicode_ci";
ELSE
SET search_collation = "utf8_bin";
END IF;
INSERT INTO TABLE1 (field1, field2)
SELECT * FROM TABLE 2 WHERE some_field LIKE '%rarf%' collate search_collation;
However, when I do this, I get
ERROR 1273 (HY000): Unknown collation: 'search_collation'
Also, if I do what's suggested at http://stackoverflow.com/questions/1680850/mysql-stored-procedures-use-a-variable-as-the-database-name-in-a-cursor-declara/2070021#2070021 I get
Dynamic SQL is not allowed in stored function or trigger
How can I use a dynamic collation?
