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

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

Answer 2

well there is a way to do it a little more code but really effective

$sql = "SELECT * FROM messages";  //your query
$result=$connvar->query($sql);    //$connvar is the connection variable
$flag=0;
     while($rows2=mysqli_fetch_assoc($result2))
    { $flag++;}
    
if($flag==0){no rows selected;}
else{
echo $flag." "."rows are selected"
}

Answer 3

Use @@ROWCOUNT:

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

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

According to SQL Server Books Online:

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

Answer 4

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

Answer 5

In my sql use information function

select FOUND_ROWS();

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

Answer 6

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

 END