[sql] 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?

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

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

SELECT count(*) as CountThis ....

Then you can compare it as string like so:

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

CHECKROW_RS is an object

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

try:

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

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

SELECT * FROM service s WHERE s.service_id = ?;
IF @@rowcount = 0
begin
select 'no data'
end

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

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

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;

SELECT * FROM service s WHERE s.service_id = ?;
BEGIN
   print 'no data'
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.