Forum Discussion

Jonathan_Hart's avatar
4 years ago
Solved

IF EXIST statement for a JDBC Alert

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
 

 

 

  • 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 :)/emoticons/smile@2x.png 2x" title=":)" width="20" /> 

    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

2 Replies

  • 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 :)/emoticons/smile@2x.png 2x" title=":)" width="20" /> 

    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

  • 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 :)/emoticons/smile@2x.png 2x" title=":)" width="20" />