SQL query to return columns and values which match part of a where condition

Question

Hi all, I am trying to find out if there's a good way to get all the column names, and values for a particular row, where a part of a condition is met. That is, I want to know which fields within my huge nested AND and OR where condition, met which conditions, and their values.

The catch is I am actually using the Dynamic LINQ API over a datatable and I will have to get it to generate that query, or do something else entirely to essentially check user-defined validation rules on some forms. If anyone has better ideas on how to approach this, I'd appreciate it.

Answer 1

Warning this is ugly as hell - but it may work for you.

Select a.*, b.*, mycond1, mycond2, mycond3
From a
    Inner Join b On a.pk = b.pk
    … rest of normal query …

        -- set of conditions --
    Left Outer Join (select 1 as matched where mycondition1) mycond1
    Left Outer Join (select 1 as matched where mycondition2) mycond2
    Left Outer Join (select 1 as matched where mycondition3) mycond3

-- Relationship between conditions
Where (mycond1.matched is not null or mycond2.matched is not null) and mycond3 is not null

The idea is to use the correlated subqueries to return a 1 or a null depending on whether the individual part of the criteria expression is true for the row. Then the logical relationship between the individual criteria expressions is applied in the where clause.

This might be doable if you're generating the SQL rather than maintaining it by hand.