Oh Roland,
I'vd just done more tests.
You are right, There shouldn't be any problem by initializing the user variables each time before using them.
I got that silly problem is because one connection(Session) is being used by many web users, which managed by the Pool. So, user variables are connection-specific, not as I thought of 'user-specific'.
http://dev.mysql.com/doc/refman/5.0/en/variables.html
Quote
User variables are connection-specific. That is, a variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.
Thanks for your help.
Now, I created a function to CONCAT the sql statement dynaimically.
IF inUsername IS NOT NULL THEN
SET varSql = joinSql(varSql, 'AND', CONCAT("username LIKE '%", inUsername, "%'"));
END IF;
CREATE FUNCTION `joinSql`(inSql TEXT, inHow TEXT, inValue TEXT) RETURNS text
BEGIN
IF (inSql <> '') THEN
SET inSql = CONCAT(inSql, ' ', inHow);
END IF;
SET inSql = CONCAT(inSql, ' ', inValue);
RETURN inSql;
END
Regards,
Ming