SQL If statement – check if row count of subquery = 1

Any suggestions on how to do this? I have a complex if statement in a query that needs to check on various conditions for a given table. EG:

IF EXISTS  (SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 0 AND Access = 'P')
      
  AND NOT EXISTS (SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 1)

  AND NOT EXISTS(SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 2)


    RETURN 1
ELSE...

However, for that first subquery, I also need to make sure it only yields one row. Exists just checks for 1 or more rows, but how do I constrain it to only return 1 if that first subquery has ONLY one row?

Answer

If you specifically need exactly one row, you can use aggregation:

IF 1 = (SELECT COUNT(*)
        FROM [xx].[dbo].[manuf]
        WHERE Lname like '@Lname' AND Approved = 0 AND Access = 'P'
       ) AND . . .

I strongly recommend EXISTS when you just need to check existence, because it is faster than aggregation.

Advertisement

Answer