Saturday, November 17, 2012 3:11 PM
Saturday, November 17, 2012 3:18 PM
One way is to use Dynamic sql. See this
Many Thanks & Best Regards, Hua Min
- Marked As Answer by Kent WaldropMicrosoft Community Contributor, Moderator Monday, December 03, 2012 2:17 PM
Saturday, November 17, 2012 4:13 PMModerator
I agree that probably the best answer here is to use dynamic SQL. Another less general solution that you might be able to take advantage of if the number of selections is small is to use CASE syntax in your order by syntax. For example:
with logEntries as ( … ) select Row, Date, Description from logEntries order by case @sortColumn when 'Row' then Row when 'Date' then Date when 'Description' then Description end
If you need to vary with both ascending and descending then additional modifications is needed. Also note that you might need to cast row as varchar and front fill it to get it to work correctly. Hang on and I will see if I can get something of a better example; my example is really a bit to crude.
On second thought, I am just going to abandon this; dynamic SQL seems like the better option anyway and unless you feel that the CASE is the way you want to go it seems best to just let this drop.
- Edited by Kent WaldropMicrosoft Community Contributor, Moderator Saturday, November 17, 2012 4:20 PM
- Proposed As Answer by Uwe RickenMicrosoft Community Contributor Saturday, November 17, 2012 6:10 PM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Saturday, November 24, 2012 12:21 PM
Saturday, November 17, 2012 5:01 PM
USE AdventureWorks2008R2 --SELECT * FROM Person.Person; DECLARE @Orderby NVARCHAR(100) ='LastName' --'FirstName' DECLARE @Str NVARCHAR(MAX); SET @Str=';WITH CTE(BusinessEntityID,LastName,Rn) AS ( SELECT BusinessEntityID,LastName ,ROW_NUMBER() OVER (ORDER BY '+ @Orderby+') AS RN FROM AdventureWorks2008R2.Person.Person ) SELECT * FROM CTE;' --PRINT @Str; EXEC SP_EXECUTESQL @Str;
Sunday, November 18, 2012 7:00 AMModerator
There are several options:
1. Use dynamic SQL to generate the entire SELECT statement
2. Use CASE expression in the ORDER BY clause like:
ORDER BY case @SortColumn when 1 then col1 end,
case @SortColumn when 2 then col2 end
3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.
There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.
TechNet Community Support
- Marked As Answer by Iric WenModerator Tuesday, December 04, 2012 1:06 AM
Sunday, November 18, 2012 8:53 AMAnswerer