Thursday, October 11, 2007

T-SQL little secrets (part 1)

Today I enriched myself with a new piece of knowledge.
Here it is:

The return type from a CASE expression is always the one and the
same, no matter which WHEN branch that is selected. The datatype
is determined by SQL Server's datatype precendence, which says that
when two types meet, the type with lower precendence is converted
to the type with higher precendence.
Basically, if you have something like this:
Select * from Users
ORDER BY
case upper(@OrderBy)
when 'USERNAME' then Username
when 'LASTNAME' then LastName
when 'FIRSTNAME' then FirstName
when 'BIRTHDATE' then BirthDate
else LastName
end
and the columns have different data types, forget it. You can't. You have to use a case for each data type:
Select * from UsersT
ORDER BY
case upper(@OrderBy)
when 'USERNAME' then Username
when 'LASTNAME' then LastName
when 'FIRSTNAME' then FirstName
else LastName
end,
case upper(@OrderBy)
when 'BIRTHDATE' then BirthDate
end

Read the complete message here.

No comments: