Thursday, January 26, 2006

Dynamic Order By's

Here is cool technique I saw here (http://www.sqlteam.com/item.asp?ItemID=2209)

Basically I knew of no way to do a dynamic order by / group by without creating dynamic sql i.e.

DECLARE @SortOrder varchar(30)
DECLARE @sql varchar(8000)
SET @SortOrder = 'CompanyName'

set @sql = 'SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY ' + @SortOrder

exec(@sql)



This sucks as SQL can not validate the syntax, the engine can not cache the execution plan, and this could open up a SQL injection attack (I am pretty sure that sql will filter / escape the incomming string though).


Anyways here is the better way to do it:



DECLARE @SortOrder tinyint
SET @SortOrder = 2

SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
ELSE ContactTitle
END




Pretty freakin sweet! There is also an interesting item on that site about creating a dynamic where clause by using COALLESCE.

0 Comments:

Post a Comment

<< Home