Convert SQL statement into mySQL stored procedure

Question

I have a piece of SQL that works out the rank of a golfer on the leaderboard. I'm pretty new to Spring and I don't think I can execute this using Spring JDBC so I think I need to convert it to a mySQL stored procedure.

Can anyone give me some pointers as to what I need to convert? Simply putting CREATE PROCEDURE and the param list around this doesn't work.

SET @rank = 1, @prev_val = NULL, @prev_rank = NULL;
SELECT rank FROM
(
 SELECT @rank := IF(@prev_val!=winnings,@prev_rank+1,@rank) AS rank
     , @prev_val := winnings AS winnings
     , @prev_rank := @rank AS prevRank
     , t.golferID
  FROM 
     (
        select g.golferID, sum(winnings) as winnings 
        from results r
        join resultDetails rd on r.resultID = rd.resultID
        join golfers g on rd.golferID = g.golferID
        where r.status = 'C' and r.compID = 1 
        group by golferID order by winnings desc
     ) AS t
) AS showRank WHERE golferID = 16

Answer 1

Your work out is fine - f00 had assumed you would want to only call out each golfers rank thru a given param - hence the construct...

create procedure get_golfer_rank
(
p_golferID int unsigned
)

and...

where golferID = p_golferID;

restricts to a single golfer.

Answer 2

this will get you started:

drop procedure if exists get_golfer_rank;

delimiter #

create procedure get_golfer_rank
(
p_golferID int unsigned
)
proc_main:begin

set @rank = 0;

select 
...
where golferID = p_golferID;

end proc_main #

delimiter ;


call get_golfer_rank(18);