Synopsis: All return values from a T-SQL CASE statement must be of the same Data Type.
…He thought the request was simple. “Before you leave [for the weekend], can you make one tiny change…it shouldn’t take more than a minute?” Or, so I heard a Friday past.
“Can we sort the results dynamically?”.
I wanted to respond that the concept of dynamic sorting would have really been better to have been brought up when the project specification was created, not after we released it in beta. I didn’t, of course. Instead, I replied with the ubiquitous, but uncertain, “suuuure?”.
Obviously, for my weekend’s entertainment, I did not want to embark on a massive overhaul of the dozen or so stored procedures involved. Long story short, I decided to create a “master” stored proc that called all the others and put all the results into a table variable for sorting. It took longer than I thought, but I was able to leave intact most of the existing SQL.
Really, there was only one issue. It was not overly complicated, but it presented quite a stumbling block.
My testing kept returning: “Conversion failed when converting date and/or time from character string.” Certainly, I was converting dates to strings and string to dates within the stored procedures, but this had never been a problem before. So, I walked through the places where there were conversions and parsed all the data, etc. Everything looked fine. But, obviously, it wasn’t. Hmmmm.
Eventually a faint memory of something I had read in SQL Sever BOL tickled my brain.
I wasn’t explicitly performing a conversion, my CASE statement was!
To explain further:
The following simplified SQL returns “Conversion failed when converting date and/or time from character string” because of the existence of multiple return data types in the same CASE statement.
The CASE statement above will attempt to convert the varchar return values to date values due to Data Type Precedence.
If you find yourself with this problem, there are two easy/quick solutions:
The best solution I came up with was to add a case statement for each possible sort.
Alternatively, you could convert all return values to the same data type within one CASE statement. In a large data set, I would not do this as the Convert function will need to occur for every record in the query. This is especially true when you have multiple WHEN statements that each need the addition of the Convert function.
Res Technicum 