How can I use function's parameter in it?

Question

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?

Answer 1

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.)