sql server store comparison operators in database and use in case statement

Question

I have a requirement to store the comparison operator symbols in sql database and then., use these comparison ooperators incase statements on other columns to render result. Can I know how can I get the comparison operatopr value in the case statement and then use them in case..

Something like .. sample table

Now, I need to apply the operator from symbol column on other columns and check if condition is satisfied , accordingly display the result. How do I achieve this?

Answer 1

You could create a function like this, which supports >,>=,<,<= and = operators:

CREATE FUNCTION func_Generic_Compare 
(

    @Param1 float,
    @Param2 float,
    @operator varchar(2)

)
RETURNS nvarchar(5)
AS
BEGIN

if @operator = '>' or @operator = '>='
   if @Param1 > @Param2 
       return 'True'

if @operator = '<' or @operator = '<='
   if @Param1 < @Param2 
       return 'True'

if @operator = '=' or @operator = '<=' or @operator = '>='
   if @Param1 = @Param2 
       return 'True'

RETURN 'False'

END  

And then use select dbo.func_Generic_Compare (col1,col2,operator) as Result from table

Using a function like this will have a serious performance impact (It's processed 'row by agonising row'!) but will be fine for small-ish datasets.

Another option would be a nested case statement. for clarity you could do it in two steps

Select col1, col2, operator,
case when operator='>' then 
    greater 
else 
    case when operator='<' then 
        less 
    else 
        equal 
    end 
end as result 

from

(Select 
     col1, 
     col2,
     operator, 
     col1>col2 as greater, 
     col1<col2 as less, 
     col1=col2 as equal
from 
compare) as PreCalculated

SQLFiddle

Another option would be

Select Col1, Col2, Col1>Col2  as Results
from Table
where operator = '>'
union all

Select Col1, Col2, Col1<Col2 
from Table
where operator = '<'
union all

Select Col1, Col2, Col1<Col2 
from Table
where operator = '='