Dynamic sorting for columns that are different data type in a store procedure
I really hate transact SQL. I just want to page some records to display on a webpage and it seems every five minutes I find something new that I have to do in the code to get the thing to work the way I’d expect it to. It just makes me despair.
The latest problem I had was I was getting a “Syntax error converting datetime from character string” when I was using the CASE statement to do a dynamic sort on the column that the user chooses.
I found a really helpful forum post that explained that if you try to use columns with different data types in a case statement SQL server will try to cast them all to the same type.
It seems the only way around it is to create a seperate case statement for each data type that you want to use and then just group the columns that are the same data type together.
The other solution I saw was that you could cast all of the sort columns to string and do a string sort on all of the columns but this isn’t a good idea because then the sort might not make sense any more.
So the original query which looked like this:
-
SELECT *FROM tableorder bycase
-
when @SortColumn='name' then Name
-
when @SortColumn='email' then Email
-
when @SortColumn='created' then CreatedDate when @SortColumn='updated' then UpdatedDateend
Needed to be rewritten to look like this:
-
SELECT *FROM tableorder bycase
-
when @SortColumn='name' then Name
-
when @SortColumn='email' then Emailend,case
-
when @SortColumn='created' then CreatedDate when @SortColumn='updated' then UpdatedDateend