Continuing the work we started at this article I was able to track down a solution for fixing both the datatype - and the asc/desc problem:
alter proc temp_GetPlayers(@nOrderBy int, @nAsc int)
as
select * from player
order by
case
when @nOrderBy = 1 and @nAsc=0 then pos
end desc,
case
when @nOrderBy = 1 and @nAsc=1 then pos
end asc,
case
when @nOrderBy = 2 and @nAsc=0 then height
end desc,
case
when @nOrderBy = 2 and @nAsc=1 then height
end asc,
case
when @nOrderBy = 3 and @nAsc=0 then namn
end desc,
case
when @nOrderBy = 3 and @nAsc=1 then namn
end asc
By using separate case statements we are able to fix both problems. Found it here at SQLTeam
