Stored Procedure with conditional results

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 开发者_C百科T.A = @a and T.B IS NULL

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

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

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

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)

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)

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?

Try this, if the first select return rows , then it returns if the first fails then the next select returns or finally the last select:

IF EXISTS(SELECT * FROM Customers 

     INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='BERJGS')
    BEGIN
             SELECT * FROM Customers 
                    INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='BERJGS'
              PRINT 'TOLA'
              RETURN
     END
ELSE
     BEGIN
               SELECT * FROM Customers 
                    INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID where Customers.CustomerID='6CHOPS'             
              IF @@ROWCOUNT > 0             
                    RETURN              
              --RETURN
     END



SELECT * FROM Customers  where Customers.CustomerID='FRANK'

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