Stored procedure - to print my result from SQL query-CodePudding

select count(*) as count
from [dbo].[DATA_received] where DATEDIFF(MINUTE, DATE_TIME  GETDATE()) <= 1

I want to run this in some frequency to check whether I am receiving data in DB.

I want to write stored procedure where if my results 0 then ,"No data received"

If the count is >0 then it should print "Data received"

CodePudding user response：

Honestly, this is probably not something that I would do in SSMS or with SQL in general. I would do this with the site or whatever you're trying to run this proc from. But...

select case when count(*) = 0 then 'No data received' else 'Data received' end as 'Message'
from [Insert table with or without contents here]

Doing something like this at the end there should do what you want. Don't forget to set this as your return value though. And no, this is not something I would ever do, but it is possible.

CodePudding user response：

Not sure how you're going to make SolarWinds do something with this output or run it every minute, but there is absolutely no reason to count how many rows were added in the last minute if you only need to know whether at least one such row exists... and you really should never apply formulae like DATEDIFF to a column, it will force the query to scan the entire table every time:

SELECT MessageToPrint = CASE WHEN EXISTS
  (
    SELECT 1 FROM dbo.DATA_received
    WHERE DATE_TIME >= DATEADD(MINUTE, -1, sysutcdatetime())
  ) THEN 'Data received.' ELSE 'No data received.' END;