Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Evaluating Boolean expressions using T-SQL

By Eli Leiba,

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) )

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
  set @currval = ParseName (@vals,@varind)
  if @currval = 'true'
    set @boolExp = replace (@boolExp,char(65+@chrind),'1')
    set @boolExp = replace (@boolExp,char(65+@chrind),'0')
  set @varind = @varind - 1
  set @chrind = @chrind + 1

create table #temp (x int)
insert #temp exec ('select ' + @boolExp)
select @x = x from #temp
if (abs(@x) = 1) 
  set @res = 'TRUE' 
  set @res = 'FALSE'
drop table #temp
set nocount off
select @res

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

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:

Total article views: 12775 | Views in the last 30 days: 2
Related Articles

TSQL Challenge 45 - Evaluate mathematical expressions presented using

This challenge involves evaluating mathematical expressions presented using Roman Numerals and retur...


TSQL Challenge 48 - Parse and evaluate Arithmetic Expressions using TS

This challenge is to parse and evaluate arithmetic expressions using TSQL.


derived column --replace " with space expression error

derived column --replace " with space expression error


Conditional operator evalutes both true & false expressions before evaluating condition

Causes an error from evaluating false expression when condition is true




advanced querying    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones