Simple CASE:
-- Example #1 SELECT RatingStars = CASE Rating WHEN 1 THEN '*' WHEN 2 THEN '**' WHEN 3 THEN '***' ELSE 'Not Rated' END FROM Book -- Example #2 -- Mapping CHAR to INT SELECT BookTypeCode = CASE BookType WHEN 'P' THEN 1 WHEN 'A' THEN 2 WHEN 'U' THEN 3 WHEN 'R' THEN 4 END FROM Book -- Example #3 DECLARE @TYPE INT, @ObjectID INT ... SELECT @TYPE = CASE WHEN SubType = 1 THEN ISNULL((SELECT ObjectType FROM Object WHERE ID=@ObjectID), 100) ELSE SubType END FROM MessageType -- Example #4 DECLARE @Name VARCHAR(100), @TYPE INT, @Price MONEY ... SET @Name = @Name + CASE WHEN @TYPE=0 THEN '-' ELSE '+' END + CASE WHEN @Price IS NULL THEN '' WHEN @Price=0 THEN '($0.00) ' ELSE '($' + CONVERT(VARCHAR, CAST(@Price AS MONEY), 1) + ') ' END
Search CASE:
-- Example #1 SELECT Title, CASE WHEN Rating >= 2 AND BookType='P' THEN 'Good programming book' WHEN Rating < 2 AND BookType='A' THEN 'Bad admin book' ELSE 'No opinion' END AS Opinion FROM Book -- Example #2 -- CASE with UPDATE...FROM and EXISTS UPDATE Book SET RatingCode = CASE WHEN EXISTS (SELECT BookId FROM Book WHERE BookType='P' AND tmp.Rating>3) THEN 200 WHEN EXISTS (SELECT BookId FROM Book WHERE BookType='A' AND tmp.Rating>4) THEN 300 ELSE 100 END FROM Book tmp
if..then..else:
-- Example #1 SELECT IsProgrammingBook = CASE WHEN BookType='P' THEN 1 ELSE 0 END FROM Book -- Example #2 SELECT AdminBookExists = CASE WHEN EXISTS (SELECT BookID FROM Book WHERE BookType='A') THEN 1 ELSE 0 END FROM Book -- Example #3 SELECT @Url = CASE WHEN IsActive=1 THEN PageUrl ELSE '' END FROM WebPage WHERE PageID=@PageID