CODEWITHSUNDEEP
sql-serverstored-procedures
Islam
Islam

Reputation: 1727

Check if a SQL Select statement returns no rows

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.

Upvotes: 35

Views: 133584

Answers (6)

Dave
Dave

Reputation: 4597

If this is SQL Server, try @@ROWCOUNT.

Upvotes: 14

Bharat Prasad Satyal
Bharat Prasad Satyal

Reputation: 79

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'

Upvotes: 7

Chethan Shetty
Chethan Shetty

Reputation: 41

Could also use an outer ISNULL check?

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453786

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

Upvotes: 70

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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

If you get 0, you got 0. :)

Upvotes: 9

user1018734
user1018734

Reputation:

try this:

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

Upvotes: 3

Related Questions