CODEWITHSUNDEEP
sqlsql-servert-sql
user776676
user776676

Reputation: 4385

How do I do an IF in Microsoft SQL SELECT query?

I am an absolute beginner in SQL. Example: I want to do a query to select people whose names begin with X if the result is 0, I want to select people whose names begin with Y.

How do I do this? Thanks.

Upvotes: 3

Views: 172

Answers (4)

onedaywhen
onedaywhen

Reputation: 57023

Here's a set-based solution:

SELECT * 
  FROM table 
 WHERE NAME LIKE 'X%'
UNION
SELECT * 
  FROM table 
 WHERE NAME LIKE 'Y%'
       AND NOT EXISTS (
                       SELECT * 
                         FROM table AS T1
                        WHERE T1.NAME LIKE 'X%'
                      );

Upvotes: 0

Filip De Vos
Filip De Vos

Reputation: 11908

I interpret the question as: show names starting with Y if no names starting with X are found. This solution will be fast as it will short-cut the exists from the moment 1 record is found

if exists(select * from table where name like 'X%')
  begin
    select * from table where name like 'X%'
  end
else
  begin
    select * from table where name like 'Y%'
  end

Ideally the name column is indexed for this to work well.

Upvotes: 3

Luka Milani
Luka Milani

Reputation: 1541

May be this is a better solution:

select * from table where name like 'X%'

if @@ROWCOUNT = 0
   select * from table where name like 'Y%'

Upvotes: 1

Steve Brownlee
Steve Brownlee

Reputation: 113

There are ways to do perform this in a single SQL statement, but they are extremely expensive (both in time and resources). You would be best served to do this kind of logic in a stored procedure.

Upvotes: 0

Related Questions