CODEWITHSUNDEEP
c#sql-servert-sqlif-statement
heyNow
heyNow

Reputation: 886

sql select statement within if condition

I want to create a statement like the following:

if (select statement1 returns rows){
   select statement2
}
else {
  select statement3
}

This if statement is part of a bigger query

   select from products where p.id in (
        if (select statement1 returns rows){
           select statement2
       }
       else {
        select statement3
      }

Is this possible in SQL Server, or is my thinking incorrect?

Upvotes: 1

Views: 3665

Answers (4)

Jason Whitish
Jason Whitish

Reputation: 1438

I'd recommend using a UNION between your two conditions to help avoid performance problems...and may be easier to read as well.

    SELECT FROM products p
    WHERE EXISTS (SELECT STATEMENT1)
    AND EXISTS (SELECT STATEMENT2 s2 WHERE s2.p_id = p.id)
    UNION
    SELECT FROM products p
    WHERE NOT EXISTS (SELECT STATEMENT1)
    AND EXISTS (SELECT STATEMENT3 s3 WHERE s3.p_id = p.id)

Depending on the nature of the statements, you might be able to leave off the Not Exists in the second select.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

Since you are checking for the same id existance, I think you could do a UNION here.

select *
from products 
where p.id in  (select id from table1
                union 
                select id from table2)

Upvotes: 1

Michael Erickson
Michael Erickson

Reputation: 4445

Actually SQL is more rigid, due to performance considerations. Your query will look more like this:

select *
from products
where  1 = case when (exists ( <statement1> ))
              then case when p.id in ( <statement2> ) then 1 else 0 end
              else case when p.id in ( <statement3> ) then 1 else 0 end
           end

Upvotes: 1

Yuck
Yuck

Reputation: 50855

You need to use a combination of EXISTS and CASE..END:

select *
from products
where p.id in (
  case when (exists ( <statement1> ))
    then ( <statement2> )
    else ( <statement3> )
  end
)

Where <statement1> might be: SELECT * FROM Customer WHERE Id = 123

<statement2> might be: SELECT MIN(field) FROM someTable

<statement3> might be: SELECT 0 as DefaultValue

If you can show some examples of what you want those actual statements to be I can provide a more concrete answer.

Upvotes: 3

Related Questions