Simple check for SELECT query empty result

Question

Can anyone point out how to check if a select query returns non empty result set?

For example I have next query:

SELECT * FROM service s WHERE s.service_id = ?;

Should I do something like next:

ISNULL(SELECT * FROM service s WHERE s.service_id = ?)

to test if result set is not empty?

Answer 1

try:

SELECT * FROM service s WHERE s.service_id = ?;

IF @@ROWCOUNT=0
BEGIN
    PRINT 'no rows!'
END

Answer 2

SELECT * FROM service s WHERE s.service_id = ?;
BEGIN
   print 'no data'
END

Answer 3

SELECT COUNT(1) FROM service s WHERE s.service_id = ?

Answer 4

SELECT count(*) as CountThis ....

Then you can compare it as string like so:

IF CHECKROW_RS("CountThis")="0" THEN ...

CHECKROW_RS is an object

Answer 5

SELECT count(*) as count FROM service s WHERE s.service_id = ?;

test if count == 0 .

More baroquely:

select case when (SELECT count(*) as count FROM service s WHERE s.service_id = ?) = 0 then 'No rows, bro!' else 'You got data!" end as stupid_message;

Answer 6

To summarize the below posts a bit:

If all you care about is if at least one matching row is in the DB then use exists as it is the most efficient way of checking this: it will return true as soon as it finds at least one matching row whereas count, etc will find all matching rows.

If you actually need to use the data for processing or if the query has side effects, or if you need to know the actual total number of rows then checking the ROWCOUNT or count is probably the best way on hand.