Terminating a program if the sql query returns with a data

  • The below query does what I need, but I need a small piece of program to execute or terminate another program if the query returns with a result.

    SELECT TOP 100 * FROM table1

    WHERE Text LIKE '%fire%'

    Order By [TimeInserted] Desc;

    The reason for this is, that I have a live emergency map. I also have another program running flyers and presentations on the reception TV screen via a dedicated Windows 10 PC. All works fine, but in case of a certain event, such as fire emergency, I want to close the presentation.exe so the live emergency map instantly will be shown on the TV screen. Any idea how can I achieve this?

  • The obvious question is from where are you running this query?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog wrote:

    The obvious question is from where are you running this query?

    From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.

    • This reply was modified 2 weeks ago by  mybne.
  • How about this, would this be helpful?

    WHILE NOT EXISTS (SELECT  * FROM table1 WHERE Text LIKE '%fire%' ) BEGIN
    WAITFOR DELAY '00:00:10'
    END;
    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
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT @Fire = CASE WHEN EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%') THEN 1 ELSE 0 END
    END
    GO

     

    • This reply was modified 2 weeks ago by  kaj. Reason: Added comment about an alternative (better?) solution
    • This reply was modified 2 weeks ago by  kaj. Reason: Typo: reate --> create
  • kaj wrote:

    How about this, would this be helpful?

    WHILE NOT EXISTS (SELECT  * FROM table1 WHERE Text LIKE '%fire%' ) BEGIN
    WAITFOR DELAY '00:00:10'
    END;
    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
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT @Fire = CASE WHEN EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%') THEN 1 ELSE 0 END
    END
    GO

    Dear Kaj,

    Sorry, I am completely lost. I do not have the deep programming knowledge you have.

    • This reply was modified 1 week, 6 days ago by  mybne.
  • From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.

    Well, you would write a program or a script in the language of your choice do this. It is not particularly advanced. If you want us to write the code for you, you need to tell us which environment you are working in.

    Then again, I suspect that to get this working well, one needs to know about your presentation program, the live emergency map and some understanding the actual business you are working in. That may be a little too many details to explain in a forum. So it may be more fruitful to find a consultant locally that can help you with this.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog wrote:

    From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.

    Well, you would write a program or a script in the language of your choice do this. It is not particularly advanced. If you want us to write the code for you, you need to tell us which environment you are working in.

    Then again, I suspect that to get this working well, one needs to know about your presentation program, the live emergency map and some understanding the actual business you are working in. That may be a little too many details to explain in a forum. So it may be more fruitful to find a consultant locally that can help you with this.

    Thank you sir,

    I wouldn't mind paying for the below piece of the program. Does it make sense like this?

    wireframe

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply