How to write a T-SQL query which can return 0 rows as part of a case statement

user_8213672 0
Viewed 683

I am trying to write a query which checks how many records arrived in the last x days so that I can send out an alert if no new records have arrived in that window.

To that end I want a query which will check the table and return either 1 row stating no files were detected if there is a problem or no rows if everything is ok. The reason I want there to be no rows is because the downstream program will treat any returned rows as an error having been detected and alert accordingly.

select 'Null check' as id,
case when
count(*) > 0 then NULL
else 'No files detected' end as Message
from TABLE where LASTUPDATEDATE > dateadd(d, -1, getdate())

This query works if an error is detected but not in the correct case as it still returns a row. How can I rewrite it so that it doesn't return anything? Thanks!

azure-sql-database tsql sql-server sql
4 Answers

For performance reasons, I would recommend writing this as:

select 'Null check' as id, 'No files detected' as Message
from (select top (1) t.*
      from table t
      where lastupdatedate > dateadd(day, -1, getdate())
     ) t
having count(*) = 0;

This saves SQL Server from actually having to count a bunch of rows if when things are fine.

user_11440350

Seems to be a task for NOT EXISTS:

select 'Null check' as id, 'No files detected' as Message
where not exists
(
  select *
  from tab
  where lastupdatedate > dateadd(day, -1, getdate())
)
user_25279050
user_63364790

You can accomplish this with a CASE and subquery:

SELECT [Message] = 
    CASE 
        WHEN COALESCE(SELECT COUNT(*) FROM [TABLE] WHERE [LASTUPDATEDATE] > DATEADD(d, -1, GETDATE())), 0) > 0
        THEN 'No files detected'
        ELSE NULL
    END ;
user_12385530
Related