Stored Procedure with conditional results

Question

I want to write a stored procedure that works something like this:

SELECT * from T where T.A = @a and T.B = @b

if that returns rows, return those rows, if not, return

SELECT * from T where T.A = @a and T.B IS NULL

Edit:

It feels that there should be a way to create a procedure such that it runs the first query once and runs the second query only if necessary.

End Edit.

The best I could manage was the follow, which (in theory) runs the first query twice, unless maybe its cached:

IF EXISTS (SELECT * from T where T.A = @a and T.B = @b) THEN
    SELECT * from T where T.A = @a and T.B = @b
ELSE
    SELECT * from T where T.A = @a and T.B IS NULL

For what its worth, this is in Microsoft SQL Server 2008

Answer 1

This should avoid the additional table access for the existence check. I'm not sure if there's a neater way.

SELECT * from T where T.A = @a and T.B = @b


IF (@@ROWCOUNT = 0)
BEGIN
    SELECT * from T where T.A = @a and T.B IS NULL
END

Answer 2

EDIT The answer was edited after the question was edited.

CREATE PROCEDURE myconditionalsp
@a  <type>,
@b  <type>
AS

SELECT * from T 
where 
    -- the second condition is true AND the first condition is false
    (((T.A = @a) and (T.B IS NULL)) AND NOT ((T.A = @a) and (T.B = @b)))
    OR 
    -- the first condition is true (regardless what is the second condition)
    ((T.A = @a) and (T.B = @b))
GO

Answer 3

You can also do it in one query:

SELECT * from T where (T.A = @a and T.B = @b) OR
( 0=(SELECT COUNT(*) T1 where (T1.A = @a and T1.B = @b) ) 
  AND T.A = @a and T.b IS NULL)

Answer 4

I think you can do this with a table variable, which should avoid the two resultsets issue. Something like:

declare @result1 table ( ... )
insert into @result1 select * from T where T.A = @a and T.B = @b

if (@@rowcount = 0)
   select * from T where T.A = @a and T.B is null
else
   select * from @result1

Answer 5

Why can't you do this in a single query:

Select ...
From T
Where T.A = @a
    And T.B = @b
Union All
Select ...
From T
Where T.A = @a
    And T.B Is Null
    And Not Exists  (
                    Select 1
                    From T
                    Where T.A = @a
                        And T.B = @b
                    )

Another single query solution:

Select ...
From T
Where T.A = @a
    And T.B = @b
Union All
(Select ...
From T
Where T.A = @a
    And T.B Is Null
Except
Select ...
From T
Where T.A = @a
    And T.B = @b)

Answer 6

I don't know it if helps at all performance-wise, but you could try table-valued function:

create function fun(@a <aType>, @b <bType>)
 returns @result (<...columns...>)
as begin
 insert into @result
 select * from T where T.A = @a and T.B = @b;

 if  (@@ROWCOUNT = 0) begin
  insert into @result
  select * from T where T.A = @a and T.B is null;
 end;
 return;
end;
GO

But I doubt it helps.

Generally I would stick with your original approach. It is the simplest and cleanest. And cache and good index should take care of performance.

If there were real performance problems here, I would step back and look at this database design. Why are you having nulls there? Why are you trying two filters? Can it be modeled differently? If not, maybe a little denormalization?