T-SQL use an entire WHRE condition coming from a string in a field table

I want to perform a running where filtering for two table using T-SQL. I can only use T-SQL. I have several conditions for the same field, depending on the sensor type.

Instead of using something like this that works for only two conditions:

SELECT
i.sensorSN,
i.measurename,
i.measureval,
r.alarmlow,
r.alarmhigh
FROM i
JOIN r
ON (i.sensorSN = r.sensorSN)
WHERE measurevalue > r.alarmhigh OR measurevalue < r.alarmlow

I would like to get the entire where condition from a table field (for instance, expression) in a reference table that includes as a string all the operators and a placeholder (%) for the comparing table field, saved like this:

"%>100 OR %<10"

Then, the expression could be evaluated like this:

SELECT
i.sensorSN,
i.measurename,
i.measureval,
r.expression
FROM i
JOIN r
ON (i.sensorSN = r.sensorSN)
WHERE «a way to EVALUATE r.expression CONDITION»

Is it nearly possible? Can you guide me to it?

The reference table is a simple JSON file like this:

[{
    "deviceserialnumber": "MyDotnetDevice",
    "sensorserialnumber": "MyDotnetDeviceHum",
    "_comment": "for instance... % is a placeholder, 2 cond OR ...",
    "expression": "%>90 OR %<30"
},
{
    "deviceserialnumber": "MyDotnetDevice",
    "sensorserialnumber": "MyDotnetDevicePressure",
    "_comment": "for instance... % is a placeholder, 2 cond AND ...",
    "expression": "%<60 AND %>50"
}, ...
]

Answer

So you can create a dynamic query by using SP_EXECUTESQL (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) and you can create the query you like. In your case, you need to evaluate multiple sensors at once, so I guess is not a one query thing, but an iteration within all sensor rather, so it would be something like this:

DECLARE @IndexNum AS INT
DECLARE @MaxCount AS INT
DECLARE @QueryText AS nvarchar(max)
DECLARE @WhereText AS nvarchar(max)
DECLARE @SQLToExec AS nvarchar(max)
DECLARE @SensorSerial AS nvarchar(max)

/*Declaring the Results Table*/
CREATE TABLE #ResultsTable (
    sensorSN nvarchar(max)
   ,measurename nvarchar(max)
   ,measureval DOUBLE PRECISION
   ,expression nvarchar(max)
)

/*Creating a list of sensors to evaluate*/
SELECT *
    ,ROW_NUMBER()  OVER(ORDER BY sensorSN ASC) 'RowIndex'
    INTO #SensorList FROM (
        SELECT DISTINCT sensorSN FROM i
        ) A
ORDER BY RowIndex ASC

/*Declaring the standard query*/
SET @QueryText = N'SELECT i.sensorSN,
                         i.measurename,
                         i.measureval,
                         r.expression
                         FROM i
                         JOIN r
                         ON (i.sensorSN = r.sensorSN)
                         WHERE i.sensorSN = '
/*Setting Max Value for the Loop*/
SET @MaxCount = (SELECT MAX(RowIndex) AS MaxVal FROM #SensorList)
/*Initializing loop index*/
SET @IndexNum = 0
WHILE @IndexNum < @MaxCount
BEGIN
    /*Setting the sensor we want to work with*/
    SET @SensorSerial = (SELECT sensorSN FROM #SensorList WHERE RowIndex = @IndexNum)
    /*Printing the sensor to know where are you working at the momment*/
    PRINT N'Working on sensor: ' + @SensorSerial
    /*Creating the dynamic Where clause*/
    SET @WhereText = (SELECT REPLACE(expression,'%','r.expression') AS 'newExpression' FROM refTable WHERE sensorserialnumber = @SensorSerial)      /*Creating the insert into the results table for the sensor evaluation*/
    SET @SQLToExec = N'INSERT INTO #ResultsTable
                           SELECT * FROM (' + @QueryText + '''' + @SensorSerial + ''' AND (' + @WhereText + '))'
    /*Executing the dynamic query*/
    EXEC sp_executesql @SQLToExec
    /*Incrementing the index*/
    SET @IndexNum = @IndexNum + 1
END

/*Showing all the results*/
SELECT * FROM #ResultsTable

/*Deleting temp tables*/
DROP TABLE #SensorList
DROP TABLE #ResultsTable

I tried to put as many comments on the code, so you’ll know what’s doing on each step.

Please update the queries with the ones you really have to get the expression from

UPDATE Added the Parenthesis to avoid conflics on the Where clause