SQL query | return null rows if data is not available for the column

I think you want something like this:

       declare @sublocation int,
                        @location int
                   set @location = 70 
                   set @sublocation = 115

                select id
                from MyTable a
                where a.location = @location
                and ( a.sublocation  = @sublocation
                      or (a.sublocation is null and 
                        not exits (select 1 
                        from Mytable sub 
                        where sub.sublocation = @sublocation)
                    ) )

You can use CTE:

SQL Fiddle

    declare @sublocation int
    declare @location int

    set @location = 70

    set @sublocation=116

    ;with cte(id, location, sublocation)
    as
    (
    select id, location, coalesce(sublocation, -9999) as sublocation
    from table1
    where location=@location
    )

    select id, location, case when sublocation=-9999 then null else sublocation end from cte
    where
    sublocation =
      case when exists(select * from table1 where sublocation=@sublocation) then
          @sublocation
      else -9999
      end

The idea here, if sublocation does not exist convert it to -9999 or some illogical integer. Then use it in comparison. The reason is, we cannot compare both "is null" and "=" in one where clause using case statement

In spite of this request being quite old, I thought I might still give an answer :-)

First of all, the given query works different than described. It works fine for sub locations not found (i.e. different from 115 in the example) by getting only the NULL entries. But for a matching sub location (i.e. 115 in the example) it gets both the matching record and the NULL records, which is not desired.

And it is very easy to reduce the result for the latter case such that only the matching entry gets selected.

select top(1) with ties 
  id, location, sub_location
from table1 
where location = @location and (sub_location is null or sub_location = @sub_location)
order by case when sub_location is null then 2 else 1 end;

So all we do is order by whether the sub-location is null or not. Then we only keep the top group, i.e. the matching entry if any, or else the NULL entries.

By the way: Does the query with sub-location really work? It looks like a subtraction of two columns (sub and location). Does this work in SQL Server? Well, I've replaced the column name with sub_location (which conforms to the SQL standard) to make sure.