Evaluating Boolean expressions using T-SQL

I wrote a procedure that can be used as a checking tools for evaluating Boolean expressions with up to 4 variable (A,B,C,D) logical operators NOT, AND ,OR are used. The evaluate Procedure gets a Boolean expression string with letters A, B, C, D and Boolean Operators AND, OR, NOT, also the number of parameters in equation and the values for each parameter separated by a dot sign.

The evaluation process replace each NOT with ~ sign, each AND with & sign and each OR with | sign. The values are parsed using the PARSENAME function and then replaced with the corresponding variable in the expression. TRUE is replaced by 1 and FALSE by 0.

The entire expression if the evaluated using dynamic T-SQL. If absolute value of result is 1 then expression is evaluated TRUE. Else a FALSE value is returned

Here is the code of this Procedure:

create proc evaluate ( @boolExp varchar(300), @numVars int, @vals varchar(30) )
as
begin
declare @varind int
declare @currval varchar(5)
declare @x int
declare @chrind int
Declare @res varchar(5)
set nocount on 
set @boolExp = replace (UPPER(@boolExp) ,'NOT','~')
set @boolExp = replace ((@boolExp) ,'OR','|')
set @boolExp = replace ((@boolExp) ,'AND','&')
set @varind = @numVars
set @chrind = 0
while @varind > 0
 begin 
  set @currval = ParseName (@vals,@varind)
  if @currval = 'true'
    set @boolExp = replace (@boolExp,char(65+@chrind),'1')
  else 
    set @boolExp = replace (@boolExp,char(65+@chrind),'0')
            
  set @varind = @varind - 1
  set @chrind = @chrind + 1
 end 
create table #temp (x int)
     
insert #temp exec ('select ' + @boolExp)
    
select @x = x from #temp
     
if (abs(@x) = 1) 
  set @res = 'TRUE' 
 else 
  set @res = 'FALSE'
      
drop table #temp
    
set nocount off
select @res
end
go

Example of using the procedure :

exec evaluate ' ( (NOT (A) AND B) OR (C AND NOT (B)) ) ',     3, 'FALSE.FALSE.TRUE'
exec evaluate ' ( (NOT (A) AND B) OR (C AND NOT (B)) ) ',     3, 'TRUE.FALSE.FALSE'

The results:

The first expression is evaluated to TRUE, and the second one to FALSE. The results are evaluated as follows:

First one:

  (NOT (FALSE) and FALSE) OR (TRUE AND NOT (FALSE)), which reduces to

  (TRUE and FALSE) OR (TRUE AND TRUE), which is

FALSE OR TRUE à  true

The second one becomes:

(not (TRUE) AND false) or (FALSE and NOT (FALSE), which becomes

(FALSE AND false) or (FALSE and TRUE, which is

(FALSE) or (FALSE) à  false

Author's Bio:

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)