sql - How to select from table that matches a search field using a search parameter with multiple search parameter

Question

I am building a string for the rest of my sql query to be run by

EXEC fuction on my SP

I am having trouble creating this string

WHERE (FullName LIKE '%[1STFullNameSearchValue]%' OR FullName LIKE '%[2ndFullNameSearchValue]%') AND (DepartmentName LIKE '%[1stDepartmentNameSearchValue]%' OR DepartmentName LIKE '%[2ndDepartmentNameSearchValue]%')

given this

CREATE TYPE [dbo].[SearchField] AS TABLE(
    [FieldName] [NVARCHAR](50) NULL,
    [SearchValue] [NVARCHAR](50) NULL
)

DECLARE @SearchFields AS SearchField;

insert into @SearchFields values('FullName', 'John')
insert into @SearchFields values('FullName', 'Karl')
insert into @SearchFields values('DepartmentName', 'Accounting')
insert into @SearchFields values('DepartmentName', 'Billing')

Expected Result

WHERE (FullName LIKE '%John%' OR FullName LIKE '%Karl%') AND (DepartmentName LIKE '%Accounting%' OR DepartmentName LIKE '%Billing%')

This is what I have

;WITH  cte_like
    (
        SELECT  
            GroupNum = CONCAT('(',STRING_AGG(CONCAT('%',SF.SearchValue,'%'),CONCAT(' OR ',SF.FieldName,' LIKE ')),')')
        FROM 
            @SearchFields SF
        GROUP BY  SF.FieldName
    )
    SELECT SearchCriteria FROM cte_grouped
    whereClause AS (
        SELECT  
            SearchCriteria = CONCAT('(',SF.FieldName, ' LIKE ' , QUOTENAME(CONCAT('%',SearchValue,'%'),''''),' OR '')')
        FROM 
            @SearchFields SF
        GROUP BY SF.FieldName
    )
    select @nSQLWhere = 'WHERE ' + STRING_AGG(SearchCriteria, ' AND ')
    from cte_where;

Answer 1

I went about it a different way marking each expression with an 'OR', 'AND' or '' based on virtualized grouping. This will work if the order of expression groups is not important. If importance is placed on the order of expressions then another level of group abstraction may be needed. You can use WITHIN GROUP (ORDER BY...) in the STRING_AGG() function and that may be all you are missing above. However, I started from scratch.

The WITHIN GROUP (ORDER BY FieldName,OrderInGroup DESC) is important because it puts the data in an order in which the first record of each group is the last record of the aggrtegation and this is needed because all the logic of the and/or/'' logic is based on item 1 being the last item in a group, reversed order.

DECLARE @SearchFields TABLE( [FieldName] [NVARCHAR](50) NULL,[SearchValue] [NVARCHAR](50) NULL)

insert into @SearchFields values('FullName', 'John')
insert into @SearchFields values('FullName', 'Karl')
insert into @SearchFields values('FullName', 'Doug')
insert into @SearchFields values('FullName', 'Foo')
insert into @SearchFields values('DepartmentName', 'Accounting')
insert into @SearchFields values('DepartmentName', 'Billing')
insert into @SearchFields values('TestTable', 'TeatsField')


;WITH Normalized1 AS
(
    SELECT
        FieldName,SearchValue,
        OrderInGroup = ROW_NUMBER() OVER (PARTITION BY FieldName ORDER BY SearchValue),
        GroupNumber = DENSE_RANK() OVER (ORDER BY FieldName DESC)
    FROM 
        @SearchFields
)
,Normalized2 AS
(
    SELECT
        *,
        WhereExpression = ''+D.FieldName+' LIKE ''%'+D.SearchValue+'%''',
        AndOrExpression =  CASE WHEN OrderInGroup <> 1 THEN ' OR ' ELSE CASE WHEN GroupNumber = 1 AND OrderInGroup = 1 THEN '' ELSE ') AND (' END END,
        OverallOrder = ROW_NUMBER() OVER(ORDER BY GroupNumber DESC, OrderInGroup DESC)
    FROM
        Normalized1 D
)
SELECT 
    WhereClause='WHERE ( '+STRING_AGG(WhereExpression + AndOrExpression, ' ') WITHIN GROUP (ORDER BY FieldName,OrderInGroup DESC)+' )'
FROM
    Normalized2

Output

WHERE ( DepartmentName LIKE '%Billing%' OR DepartmentName LIKE '%Accounting%') AND ( FullName LIKE '%Karl%' OR FullName LIKE '%John%' OR FullName LIKE '%Foo%' OR FullName LIKE '%Doug%') AND ( TestTable LIKE '%TeatsField%' )