Check if a SQL Select statement returns no rows

Question

I have a select statement

SELECT     QBalance
FROM         dbo.CustomerBalance
WHERE     (CustomerID = 1) AND (MarchentID = @MerchantId)

I want to check if that statement returns 0 rows. I tried to use the ISNULL and IFNULL but it seems that I'm missing something.

Answer 1

To find out whether no matching rows exist you can use NOT EXISTS. Which can be more efficient than counting all matching rows

IF NOT EXISTS(SELECT * FROM ...)
BEGIN
PRINT 'No matching row exists'
END

Answer 2

If this is SQL Server, try @@ROWCOUNT.

Answer 3

SELECT    COUNT(*) 
FROM         dbo.CustomerBalance 
WHERE     (CustomerID = 1) AND (MarchentID = @MerchantId) 

If you get 0, you got 0. :)

Answer 4

You can use @@ROWCOUNT. For e.g.

SELECT     QBalance
FROM         dbo.CustomerBalance
WHERE     (CustomerID = 1) AND (MarchentID = @MerchantId)

--This will return no of rows returned by above statement.
SELECT @@ROWCOUNT

You will get 0 if first statement will not return any rows. You can also use if statement to check that just after first statement. e.g.

IF @@ROWCOUNT <> 0 
  PRINT 'Select statement is returning some rows'
ELSE 
  PRINT 'No rows returned'