• 0

IF EXIST statement for a JDBC Alert


Jonathan Hart
 Share

Question

Hello,

I'm trying to setup an alert that runs a query against an  MSSQL DB and in a couple of cases the DB name is different.

The intial thought was that I could add IFEXIST (DB name query) then begin a query and then at the end of that query have another IFEXIST (the other DB name query) then begin but this then creates an error saying the DB doesn't exist for the other DB.

Example:

The error that occurs for the below is when polling the data on server 1 it is saying database 456 does not exist which is correct but i was hoping it would ignore it because of the IFEXIST clause.
server 1 has a database called 123 and server 2 has the same database but it's called 456.

So I have tried the below:

IF EXISTS (select * from sys.databases where name = '123')
BEGIN
USE 123
SELECT CAST
(
    (
        Select COUNT(*)
        from table
        where ARR_TIMESTAMP >= DATEADD(day, -1, GETDATE())
        and column LIKE '%flowtype1%'
        )
        AS INT
) AS [flowtype1],
CAST
(
    (
        Select COUNT(*)
        from table
        where ARR_TIMESTAMP >= DATEADD(day, -1, GETDATE())
        and column LIKE '%flowtype2%'
        )
    AS INT
) AS [flowtype2]
END;
ELSE

IF EXISTS (select * from sys.databases where name = '456')
BEGIN
USE 456
SELECT CAST
(
    (
        Select COUNT(*)
        from table
        where ARR_TIMESTAMP >= DATEADD(day, -1, GETDATE())
        and column LIKE '%flowtype1%'
        )
        AS INT
) AS [flowtype1],
CAST
(
    (
        Select COUNT(*)
        from table
        where ARR_TIMESTAMP >= DATEADD(day, -1, GETDATE())
        and column LIKE '%flowtype2%'
        )
    AS INT
) AS [flowtype2]
END;


Any help would be appreciated.

Thanks
Jonny
 

 

 

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
2 hours ago, Vitor Santos said:

Not sure if I'm understanding it correctly but, I believe if it returns an error it'll abort the operation & return you the exception.
My suggestion is to use try, catch (via groovy instead)... to predict those possible scenario(s) & don't abort the script without following the remaining operations.

Not sure if this will help you, but, just a suggestion :) 

Thanks for the response Vitor, 

Did a little more digging and found the best way to do it (in this case, not necessarily the best way) was to add DECLARE @SQL NVARCHAR(MAX); and then print @SQL at the end so: 

--PRINT @SQL
EXECUTE sp_executesql @SQL
END

This then allowed me to use multiple DB names and it wouldn't error when the DB didn't exist for different server.

Thanks
Jonny

  • Like 1
Link to comment
Share on other sites

  • 0

Not sure if I'm understanding it correctly but, I believe if it returns an error it'll abort the operation & return you the exception.
My suggestion is to use try, catch (via groovy instead)... to predict those possible scenario(s) & don't abort the script without following the remaining operations.

Not sure if this will help you, but, just a suggestion :) 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share