Take the 2-minute tour ×

I'm trying to solve this issue with only one select. It can be easily solved with 2, but I want only one:

Imagine this simple table:

create table CheckTest(
    name varchar(50)

I want a query that fail (it will be inside an if statement) if there isn't any rows on the table or there is a row with name='test'

So, these are the possible scenarios:

--scenario 1 - no rows: should fail
truncate table CheckTest

--scenario2 - rows with value different than "test": should succeed
insert into CheckTest values ('OK')
insert into CheckTest values ('OK')

--scenario3 - 1 row with value "test" or 1 row with value "test" among other rows with value OK

insert into CheckTest values ('test')
insert into CheckTest values ('OK')
insert into CheckTest values ('OK')

I'm planning on using the check like this:

if (<check>)
    print 'fail'
    print 'continue'
share|improve this question
Diego, you query should do what? select 'fail' or 'success' depending on the cases? –  aF. Feb 7 '12 at 11:13
yes, its a stop condition inside a proc. I just edited the question adding this information. thanks –  Diego Feb 7 '12 at 11:19
add comment

2 Answers

up vote 2 down vote accepted

It is possible like this:

    if (select count(c1.name) + case when count(*) = 0 then 1 else 0 end
        from #CheckTest c
        left join #CheckTest c1 on c.name = c1.name and c1.name = 'test' )  > 0
       select 'fail'
      select 'success'
share|improve this answer
works but I would like to do it with only one select. Both check on the "where" condition. I was wondering if it is possible to check a condition and if a line exist at the same time. –  Diego Feb 7 '12 at 11:29
@Diego, it's possible, check my edited answer. –  aF. Feb 7 '12 at 11:40
add comment

It is possible but I wouldn't bother.

All the ways I can think of that combine this into one SELECT (e.g. using COUNT or DENSE_RANK) are less efficient than two separate queries. One way below.

              FROM   CheckTest 
                                    WHEN name = 'test' THEN 1 
                                    ELSE 2 
                                  END) = 2) 
  PRINT 'fail' 
  PRINT 'continue' 
share|improve this answer
add comment

Your Answer


