Mysql – How to use function’s parameter in it

functionsMySQL

I have a function like this:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of WEEK. Here is my new function:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

But it throws this error:

The following query has failed:

CREATE  DEFINER=`root`@`localhost` FUNCTION `user_repo`(`user_id` INT,
                `range` VARCHAR(10) CHARSET utf8)
       RETURNS VARCHAR(20) CHARSET utf8mb4
       NOT DETERMINISTIC
       CONTAINS SQL SQL
       SECURITY DEFINER
BEGIN DECLARE vote_value mediumint;
DECLARE score mediumint;
SELECT  coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
    INTO  vote_value, score
    FROM  reputations
    WHERE  owner_id = user_id
      AND  date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));
RETURN CONCAT_WS(', ', vote_value, score);
END

MySQL said: #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9

Does anybody what's wrong?

Best Answer

Certain parts of queries cannot be substituted like that. Such parts include table names and keywords. "WEEK" fall into this category. Instead...

-- Construct the query:
SET @sql = CONCAT("SELECT ... INTERVAL 1 ", range, "))");
-- perform it:
PREPARE _query FROM @sql;
EXECUTE _query;
-- clean up:
DEALLOCATE PREPARE _query;

(This will probably be more efficient than some IF..THEN..ELSE or CASE..WHEN construct.)

Related Question