Published 7/5/2007 11:25:42 PM  in  SQLServer

Consider this table:

Now say we want to create a stored procedure to read from it - and we want to be able to specify which column to sort on.

A solution like this:

create proc temp_GetPlayers(@sOrderBy varchar(20))
as
exec('select * from player order by ' + @sOrderBy )

would let us run things like:

temp_GetPlayers 'pos'

temp_GetPlayers 'height'

and even

temp_GetPlayers 'pos desc'

However dynamically constructing sql and EXECuting them are not recommended, there are performance considerations (no precompilation) and also it opens up for sql injection. What if the @sOrderBy was taken from ASP.NET Request[] collection - it would then be possible to send in "pos; delete from immortantable" in the querystring and that's a classic sql injection...

Lets try to use integers instead:

alter proc temp_GetPlayers(@nOrderBy int)
as
select * from player 
order by 
	case when @nOrderBy = 1 then pos
		when @nOrderBy = 2 then shoots
		else namn
	end
		

Using integers is injection safe. We call it like temp_GetPlayers 1, temp_GetPlayers 2 etc.

However - we do have a problem with this approach...The case statement can only work with one sinle datatype - meaning we cant do this:

alter proc temp_GetPlayers(@nOrderBy int)
as
select * from player 
order by 
	case when @nOrderBy = 1 then pos
		when @nOrderBy = 2 then shoots
		when @nOrderBy = 3 then height
		else namn
	end

you will now get an error like

Syntax error converting the varchar value 'L' to a column of data type int.

So, the solution is to cast:

alter proc temp_GetPlayers(@nOrderBy int)
as
select * from player 
order by 
	case when @nOrderBy = 1 then pos
		when @nOrderBy = 2 then shoots
		when @nOrderBy = 3 then CAST(height AS VARCHAR(50))
		else namn
	end

Still, this technique is still not optimal - we can't dynamically specify asc/desc ordering. However, as always, the solution is not optimal - but -  it might be good enough.