SQL query using variable in the where clause for exclusions

Question

I am using dbWarden (https://www.sqlservercentral.com/articles/dbwarden-a-free-sql-server-monitoring-package-3) for SQL monitoring and would like for certain procs to be excluded from the "long running queries" job based on an exclusion list table. I am trying to list a bunch of SQL_TEXT strings in a table that should be excluded from my query in the WHERE clause.

The portion of the proc I am editing looks like this:

DECLARE @exclusions NVARCHAR (MAX);
SELECT @exclusions = COALESCE(@exclusions + '%''' +  ' and SQL_TEXT not like ''%' + SQL_TEXT , SQL_TEXT) FROM LongRunningQueriesExclusions 

    
    
    
SELECT * FROM QUERYHISTORY
                WHERE RunTime >= 10
                AND [DBName] NOT IN (SELECT [DBName] COLLATE DATABASE_DEFAULT FROM [DBATools].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
                AND Formatted_SQL_Text NOT LIKE '%BACKUP DATABASE%'
                AND Formatted_SQL_Text NOT LIKE '%RESTORE VERIFYONLY%'
                AND Formatted_SQL_Text NOT LIKE '%ALTER INDEX%'
                AND Formatted_SQL_Text NOT LIKE '%DECLARE @BlobEater%'
                AND Formatted_SQL_Text NOT LIKE '%DBCC%'
                AND Formatted_SQL_Text NOT LIKE '%FETCH API_CURSOR%'
                AND Formatted_SQL_Text NOT LIKE '%WAITFOR(RECEIVE%'
                -- MY ADDITION ------------------------------------------------------
                AND SQL_TEXT not LIKE  '''%' + @exclusions + '%'''

My exclusions table has the following procs I want to exclude: LongRunningQueriesExclusions Table

When printing the @exclusions variable it returns: SP_testProc%' and SQL_TEXT not like '%SP_AnotherTestProc%' and SQL_TEXT not like '%SP_OneMoreTest

The query returns what I expect when replacing the variable with the printed text but does not work with the variable itself.

Is there a way to include my exclusions list with the wildcards in the WHERE clause of my query?

Answer 1

There's no need to build this statement dynamically, you can just reference your exclusions table from the main query directly and use NOT EXISTS to remove records with SQL text that contains values from there. e.g.

AND NOT EXISTS
    (   SELECT  1
        FROM    LongRunningQueriesExclusions AS exc
        WHERE   QUERYHISTORY.SQL_TEXT LIKE CONCAT('%', exc.SQL_TEXT , '%')
    );

As an aside, you should not use variable assignment to concatenate strings, the behaviour is unreliable and undocumented you should use STRING_AGG, e.g.

DECLARE @exclusions NVARCHAR (MAX);
SELECT @exclusions = STRING_AGG(' SQL_TEXT not like ''%' + SQL_TEXT + '%''', ' AND ') 
FROM LongRunningQueriesExclusions;