how do I do this?

I want to change the order by criteria depending on a certain input paramater

So suppose

This is a simplified condensed version of what I need

declare @.sortID int
set @.sortID=1

So if the @.sortID=1 I wanna have this

select * from tblusersorder by name

else

select * from tblusers whereorder by age

so depending on the sort id the bolded text should change.

Only one way I know of ...

declare @.sortID int
set @.sortID=1
If (@.sortID = 1)
BEGIN
select * from tblusersorder by name
END
ELSE
BEGIN
select * from tblusers whereorder by age
END

I do not believe order by can be a parameter

|||

hello,

and other way to do, dynamic sql execution, some case is not good way to do but versstile

===============================

1declare @.idint2declare @.sqlnvarchar(max)34set @.id = 156set @.sql ='select top 10 productid, [name] from production.product '7+'order by ' +case @.id8when 1then'[name]'9when 2then'productid'10end1112execsp_executesql @.sql

===============================

|||busyweb is right. You could use dynamic SQL. I just never think of dynamic SQL because of the speed, security, maintainability problems. (smile)