How about this, would this be helpful?
WHILE NOT EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%' ) BEGIN
WAITFOR DELAY '00:00:10'
THROW 50000,'Some error message',1
It should loop indefinitely every 10 second until a rowset is returned. In that case it will thow an error, and this in turn can be checked for in the client and the appropriate measures taken.
But on the other hand I would argue that this loop should happen in the application itself. That would free the connection from being tied up while doing the wait, for example.
You could create a small stored procedure that returns a boolean output parameter that can be checked by the client application, and if no data is available, i.e. the boolean/bit parameter is false, the client goes to sleep for a number of seconds and then repeats the call. If true is returned, some shutdown code can be executed.
CREATE PROCEDURE dbo.usp_CheckForFire
@Fire bit out
SET NOCOUNT ON;
SELECT @Fire = CASE WHEN EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%') THEN 1 ELSE 0 END