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 first expression is evaluated to TRUE, and the second one to FALSE. The results are evaluated as follows:
(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
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: firstname.lastname@example.org)