CREATE PROCEDURE Ranking
@username varchar(50)
AS
declare @tname varchar(100);
set @tname =’tips_’+@username ;
declare @strex nvarchar(2000);
set @strex=’select isnull((sum(game1)+sum(game2)+sum(game3)+sum(game4)+sum(game5)+sum(game6)+sum(game7)+sum(game8)+sum(game9))/count(game1),0) from(
select case ua.game1
when 0 then 0
else ua.game1-res.game1
end as game1,case ua.game2
when 0 then 0
else ua.game2-res.game2
end as game2,case ua.game3
when 0 then 0
else ua.game3-res.game3
end as game3,case ua.game4
when 0 then 0
else ua.game4-res.game4
end as game4,case ua.game5
when 0 then 0
else ua.game5-res.game5
end as game5,case ua.game6
when 0 then 0
else ua.game6-res.game6
end as game6,case ua.game7
when 0 then 0
else ua.game7-res.game7
end as game7,case ua.game8
when 0 then 0
else ua.game8-res.game8
end as game8,case ua.game9
when 0 then 0
else ua.game9-res.game9
end as game9
from results res ,’+@tname+’ ua where res.roundID=ua.roundID) as a;’;
print @strex;
execute(@strex);
