today's item of stupidity is the dynamic orderby clause. i see this particular abomination all over the place when i'm refactoring procs, reports, etc. firstly, why is this bad? firstly, most people include a null check and then build a dyanmic sql statement to execute. dynamic sql should always be avoided if possible. secondly, using this methodology leaves you vulnerable to injection attacks. finally for me, sorting can generally be done on the front end. normally the user wants to sort and resort etc, why would i want to sort it on the back end when they will just resort it 15 times on the front end. if you find that you are passing like sort by 15 different fields to a proc like this, i would generally tell you that you are stupid and need to rethink your "solution."
on to the code.
stupidcreate proc getstuffinorder(@order varchar(100)) as
declare @sql varchar(max)
set @sql = 'select f1, f2, f3 from t'
if (@order is not null and ltrim(@order) is not '' )
set @sql = @sql + ' order by ' + @order
exec @sqlnot stupid
create proc getstuffinorder(@order varchar(100)) as
select f1, f2, f3 from t order by
case @order
when 'f1' then f1
when 'f2' then f2
when 'f3' then f3
else 1
end