TSQL -- Make it better

Question

I have following TSQL, 3 IDs (@EmpID, @DeptID and @CityID) are passed in and it could all have NULL or any one could be NULL. See following scenario:

-- Very Narrow (all IDs are passed in)
IF(@EmpID IS NOT NULL AND @DeptID IS NOT NULL AND @CityID IS NOT NULL)
   BEGIN    
      SELECT
         e.EmpName 
         ,d.DeptName
         ,c.CityName
      FROM
         Employee e WITH (NOLOCK)
         JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
         JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
      WHERE 
         e.EmpID = @EmpID
   END
-- Just 2 IDs passed in
ELSE IF(@DeptID IS NOT NULL AND @CityID IS NOT NULL)
   BEGIN
      SELECT
         e.EmpName 
         ,d.DeptName
         ,NULL AS [CityName]
      FROM
         Employee e WITH (NOLOCK)
         JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
         JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
      WHERE 
         d.deptID = @DeptID
   END
-- Very Broad (just 1 ID passed in)
ELSE IF(@CityID IS NOT NULL)
   BEGIN
      SELECT
         e.EmpName 
         ,NULL AS [DeptName]
         ,NULL AS [CityName]
      FROM
         Employee e WITH (NOLOCK)
         JOIN Department d WITH (NOLOCK) ON e.deptid = d.deptid
         JOIN City c WITH (NOLOCK) ON e.CityID = c.CityID
      WHERE 
         c.CityID = @CityID
   END
-- None (Nothing passed in)
ELSE 
   BEGIN
      SELECT
         NULL AS [EmpName]
         ,NULL AS [DeptName]
         ,NULL AS [CityName]
   END

Question: Is there any better way (OR specifically can I do anything without IF...ELSE condition?

Answer 1

there are many ways to do this. the key is to produce a query that will use an index.

some of the common ones are:
x = @x OR @x IS NULL
Using IF statements
eliminate nulls by using min and max values
Dynamic SQL
OPTION (RECOMPILE), on certain versions of SQL Server 2008

This link will explain those and several more: http://www.sommarskog.se/dyn-search.html

Have a look at previous, yet similar questions:
Optimal search queries

Implementing search functionality with multiple optional parameters against database table

In your particular code example you may want to add dbo. or whatever the schema is on to each table name so each user does not get their own version in cache see: http://www.sommarskog.se/dynamic_sql.html#queryplans

You might also need to worry about parameter sniffing, see: http://www.sommarskog.se/dyn-search-2005.html#OR_ISNULL