CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Adam Tegen
Adam Tegen

Reputation: 25887

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 T.A = @a and T.B IS NULL

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

Upvotes: 8

Views: 8685

Answers (7)

BobSpring
BobSpring

Reputation: 87

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'

Upvotes: 0

eKek0
eKek0

Reputation: 23289

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

Upvotes: -2

Thomas
Thomas

Reputation: 64635

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)

Upvotes: 1

Tomek Szpakowicz
Tomek Szpakowicz

Reputation: 14512

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?

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103447

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

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

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)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453018

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

Upvotes: 5

Related Questions