ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri