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 endAs 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!