CODEWITHSUNDEEP
sqlsql-serverdatabase
Denys S.
Denys S.

Reputation: 6525

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?

Upvotes: 86

Views: 356420

Answers (12)

Karan Shrivastav
Karan Shrivastav

Reputation: 1

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

Upvotes: 0

csandreas1
csandreas1

Reputation: 2388

SELECT count(*) as CountThis ....

Then you can compare it as string like so:

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

CHECKROW_RS is an object

Upvotes: 0

Viranja kaushalya
Viranja kaushalya

Reputation: 785

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

Upvotes: 2

Samim Hussain
Samim Hussain

Reputation: 416

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

Upvotes: 12

Sunil Kumar
Sunil Kumar

Reputation: 665

In my sql use information function 

select FOUND_ROWS();

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

Upvotes: 0

ovais.tariq
ovais.tariq

Reputation: 2625

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

Upvotes: 4

Donnie
Donnie

Reputation: 46933

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.

Upvotes: 3

KM.
KM.

Reputation: 103677

try:

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

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

Upvotes: 7

marc_s
marc_s

Reputation: 755227

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.

Upvotes: 104

Raja
Raja

Reputation: 3618

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

Upvotes: 14

tpdi
tpdi

Reputation: 35171

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;

Upvotes: 0

Ed B
Ed B

Reputation: 6054

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

 END

Upvotes: 132

Related Questions