return only the last select results from stored procedure

Question

The requirement says: stored procedure meant to search data, based on 5 identifiers. If there is an exact match return ONLY the exact match, if not but there is an exact match on the not null parameters return ONLY these results, otherwise return any match on any 4 not null parameters... and so on

My (simplified) code looks like:

create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)...
as 
begin
  select whatever 
    from MyTable t
    where ((@a is null and t.a is null) or (@a = t.a)) and
          ((@b is null and t.b is null) or (@b = t.b))...

    if @@ROWCOUNT = 0
    begin
        select whatever 
          from MyTable t
          where ((@a is null) or (@a = t.a)) and
                ((@b is null) or (@b = t.b))...
          if @@ROWCOUNT = 0
          begin
             ...
          end
    end
end

As a result there can be more sets of results selected, the first ones empty and I only need the last one. I know that it is easy to get the only the last result set on the application side, but all our stored procedure calls go through a framework that expects the significant results in the first table and I'm not eager to change it and test all the existing SPs.

Is there a way to return only the last select results from a stored procedure? Is there a better way to do this task ?

Answer 1

You could use a local table variable to hold the results and then SELECT from that, so there is only one SELECT.

You could repeat your queries (you end up being able to get rid of nesting):

create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)... 
as  
begin 
  IF EXISTS (select whatever  
    from MyTable t 
    where ((@a is null and t.a is null) or (@a = t.a)) and 
          ((@b is null and t.b is null) or (@b = t.b))... )
  BEGIN
    select whatever  
        from MyTable t 
        where ((@a is null and t.a is null) or (@a = t.a)) and 
              ((@b is null and t.b is null) or (@b = t.b))... 
    RETURN
  END

   etc. 
end 

Or you can find a way to combine all the queries into one query - possibly with a UNION.

Answer 2

Use a table variable:

create procedure xxxSearch @a nvarchar(80), @b nvarchar(80)...
as 
begin
  DECLARE @res TABLE(...)
  INSERT INTO @res(...)
  select whatever 
    from MyTable t
    where ((@a is null and t.a is null) or (@a = t.a)) and
          ((@b is null and t.b is null) or (@b = t.b))...

    if @@ROWCOUNT = 0
    begin
        INSERT INTO @res(...)
        select whatever 
          from MyTable t
          where ((@a is null) or (@a = t.a)) and
                ((@b is null) or (@b = t.b))...
          if @@ROWCOUNT = 0
          begin
             ...
          end
    end
    SELECT ... FROM @res
end