How can I use function's parameter in it?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply