CODEWITHSUNDEEP
mysqlsqlstored-proceduresparameter-passing
Martin AJ
Martin AJ

Reputation: 6697

How can I pass parameter to stored procedure through a query?

I have this stored procedure:

CREATE PROCEDURE `test`(OUT `vote_value` INT, OUT `score` INT, IN `user_id` INT, IN `time_range` INT)
    NO SQL
BEGIN
    SELECT coalesce(sum(vote_value), 0), coalesce(sum(score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > CASE time_range
          WHEN 'WEEK' 
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
          WHEN 'MONTH' 
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 MONTH))
          WHEN 'YEAR' 
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
          ELSE 1 END;
END

As you see my procedure gets 4 parameters. Both vote_value and score have OUT direction (as result), And both user_id and time_range have IN Direction. So I have to pass those two which have IN direction when I call the procedure.

Ok, I can call procedure about like this:

CALL test(@vote_value, @score, 10, 'WEEK');
SELECT @vote_value, @score;

It would work as well. But please focus on the third parameter which is 10. I don't want to pass it manually by hand. I want it be a value from a query. Something like this:

CALL test(@vote_value, @score, u.id, 'WEEK');
SELECT id, @vote_value, @score FROM Users u;

But it throws syntax error. Does anybody know how can I do such a thing? The expected result is this.

Upvotes: 0

Views: 1050

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7980

SELECT id into @id FROM Users LIMIT 1;
CALL test(@vote_value, @score, @id, 'WEEK');

Try above code.

As mention in above code you can get value of that id using SELECT id into @id and then use that @id as parameter for that query.

Upvotes: 0

Related Questions