SQL Server: build dynamic WHERE and ORDER BY clauses

For a recent project, I needed to build a dynamic SELECT statement without using lots of IF statements or resort to dynamic SQL statements, with the EXEC statement. Well, some research came to the rescue.

In my use case, the user chooses a search column from a dropdown box and then provides a search value. So the WHERE clause needs to be dynamic. This article provided the solution: using a combination of OR and AND statements to account for null values:

select * from Employee
 where 
   (lastName = @LastName AND @LastName IS NOT NULL)
  OR (employeeID = @employeeID AND @employeeID IS NOT NULL)
  OR (securityID = @securityID AND @securityID IS NOT NULL)
 order by
  case when @LastName is not null then CONVERT(char(50), lastName) + CONVERT(char(50), firstName) 
   when @employeeID is not null then employeeID 
   when @securityID is not null then securityID
  end

As for the ORDER BY clause, this article solved it. A CASE statement. Note also the multi-value option for the lastName column: I'd like to sort on lastName and then firstName. This is not allowed in an ORDER BY CASE statement; however, thanks to the 4 Guys from Rolla, the trick is to use string concatenation to solve this. Happy coding!