What's Wrong With this Code? (#2)

Tuesday, August 8, 2006

A developer wanted to keep track of some birthdays with the following table design and data.

CREATE TABLE [Birthdays]
(
  [Name] varchar(50),
  [BirthDate] datetime   
)

INSERT INTO [Birthdays] VALUES('Gene Wilder',  '6/11/1935')
INSERT INTO [Birthdays] VALUES('Nicola Tesla', '7/9/1856')
INSERT INTO [Birthdays] VALUES('Miles Davis',  '5/26/1926')

To sort the data by name and by date, the developer wrote a single stored procedure to handle both cases.

CREATE PROC GetBirthdays
  @Ordering int
AS
BEGIN
  SELECT [Name], [BirthDate] FROM [Birthdays]
  ORDER BY
    CASE @Ordering
      WHEN 1 THEN [Birthdate]
      WHEN 2 THEN [Name] 
    END
END 

The developer tested the proc by passing a value of 1, and was pleased to see a resultset ordered by birth date.

What can go wrong? (Hint: Try passing a 2).

How could the developer fix the problem by changing only 1 line?

All My Pluralsight Courses

OdeToCode by K. Scott Allen