Constructing SQL Select Query from the conditions in other table

Question

Help required for a SQL Server query.

Users table:

UserID      FirstName  LastName
----------- ---------- ----------
1           Sasasd     asdfhy
2           Sachnahi   Jhootey
3           csajd      mkjcy
4           Scsajd     aiuyay
5           csdcsajd   aiuyawe

Custom query table:

FieldOrigin|FieldID|FieldType|FieldCondition|FieldNumberValue|FieldTextValue
1          |2      |1        |2             |null            |s
1          |3      |1        |3             |null            |y

• FieldType: 1 = Text, 2 = Number
• FieldCondition : 1 - Contains, 2 - Starts with, 3 - Ends with, 4 - Equals

I need help in writing query to get UserID's from the Users table, which abide to the conditions in the custom query table.

Query: find the user ids from Users whose names start with s and ends with y

I tried writing this query but this isn't working:

SELECT U.UserID 
FROM CUSTOM_TABLE C, Users U
WHERE C.FieldOrigin = 1
  AND (CASE C.FieldID
          WHEN 2 
             THEN 
                (CASE 
                    WHEN C.FieldCondition = 1 AND U.FirstName LIKE '%' + C.FieldTextValue +'%'  
                       THEN 1
                    WHEN C.FieldCondition = 2 AND U.FirstName  LIKE C.FieldTextValue +'%' 
                       THEN 1
                    WHEN C.FieldCondition = 3 AND U.FirstName LIKE '%' + C.FieldTextValue 
                       THEN 1
                    WHEN C.FieldCondition = 4 AND U.FirstName = C.FieldTextValue 
                       THEN 1
                    ELSE 0
                 END)
          WHEN 3 
             THEN 
                (CASE 
                    WHEN C.FieldCondition = 1 AND U.LastName LIKE '%' + C.FieldTextValue +'%' 
                       THEN 1
                    WHEN C.FieldCondition = 2 AND U.LastName LIKE C.FieldTextValue +'%' 
                       THEN 1
                    WHEN C.FieldCondition = 3 AND U.LastName LIKE '%' + C.FieldTextValue 
                       THEN 1
                    WHEN C.FieldCondition = 4 AND U.LastName = C.FieldTextValue 
                       THEN 1
                    ELSE 0
                 END)
            ELSE 0
    END) = 1
GROUP BY 
    U.UserID

This returns 1,2,3,4 but the correct answer is 1,2,4

Fiddle Link

Answer 1

declare @query varchar(1000)
declare @i int = 0
Set @query  = 'SELECT U.UserID from  Users U where ' 
select  @query  = @query + case @i when 0 THEN '' ELSE ' AND ' END +
CASE C.FieldID
WHEN 2 THEN 
    (CASE 
        WHEN C.FieldCondition = 1 THEN 'U.FirstName LIKE ''%' + C.FieldTextValue +'%'''
        WHEN C.FieldCondition = 2 THEN 'U.FirstName LIKE ''' + C.FieldTextValue +'%'''
        WHEN C.FieldCondition = 3 THEN 'U.FirstName LIKE ''%' + C.FieldTextValue +''''
        WHEN C.FieldCondition = 4 THEN 'U.FirstName =''' + C.FieldTextValue +''''
        ELSE ''
    END)
WHEN 3 THEN 
    (CASE 
        WHEN C.FieldCondition = 1 THEN 'U.LastName LIKE ''%' + C.FieldTextValue +'%'''
        WHEN C.FieldCondition = 2 THEN 'U.LastName LIKE ''' + C.FieldTextValue +'%'''
        WHEN C.FieldCondition = 3 THEN 'U.LastName LIKE ''%' + C.FieldTextValue +''''
        WHEN C.FieldCondition = 4 THEN 'U.LastName =''' + C.FieldTextValue +''''
        ELSE ''
    END)
ELSE ''
END, @i=@i+(
              CASE WHEN (C.FieldID = 2 OR C.FieldID = 3) AND 
                  (C.FieldCondition = 1 OR C.FieldCondition = 2 OR C.FieldCondition = 3 OR C.FieldCondition = 4) THEN 1
              ELSE 0 END
            )
from CUSTOM_TABLE C where C.FieldOrigin = 1

exec  (@query)

It is not elegant solution but it works