Simple check for SELECT query empty result

Can anyone point out how to check if a select query returns non empty resul开发者_如何学Got 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

---

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.

---

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

---

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

---

try:

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

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

---

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

---

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 * FROM service s WHERE s.service_id = ?;
IF @@rowcount = 0
begin
select 'no data'
end

---

SELECT count(*) as CountThis ....

Then you can compare it as string like so:

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

CHECKROW_RS is an object

---

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;

---

In my sql use information function

select FOUND_ROWS();

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

---

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"
}