Simple check for SELECT query empty result

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?

IF EXISTS(SELECT * FROM service s WHERE s.service_id = ?)
BEGIN
--DO STUFF HERE

END

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;

I agree with Ed B. You should use EXISTS method but a more efficient way to do this is:

IF EXISTS(SELECT 1 FROM service s WHERE s.service_id = ?)
BEGIN
--DO STUFF HERE

END

HTH

Use @@ROWCOUNT:

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

IF @@ROWCOUNT > 0
-- do stuff here.....

According to [SQL Server Books Online][1]:

> Returns the number of rows affected by
> the last statement. If the number of
> rows is more than 2 billion, use
> ROWCOUNT_BIG.


[1]:

[To see links please register here]

try:

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

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

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.

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

In my sql use information function

select FOUND_ROWS();

it will return the no. of rows returned by select query.

You can do it in a number of ways.

IF EXISTS(select * from ....)
begin
-- select * from ....
end
else
-- do something

Or you can use `IF NOT EXISTS , @@ROW_COUNT` like

select * from ....
IF(@@ROW_COUNT>0)
begin
-- do something
end