Tuesday, June 28, 2005

Avoiding "Case When" From your TSQL in MS-SQLServer

Today I was working on a query with a conditional construct where i had to use CASE WHEN. But a "Case when" will be evaluted for each record.
Keeping this thought was looking for a new logic. At last i arrived that



SELECT FirstName, LastName, Status
FROM RequestTable
Inner join
(
Select -1 as Appr , 'New' as Status
Union
Select 0, 'Created'
Union
Select 1, 'Accepted'
Union
Select 2, 'Rejected'
) T

On IsNull(Cast(Approved as smallint),-1) = Appr

No comments: