Boolean expressions are an essential part of SQL Server programming. They allow us to evaluate conditions and make decisions based on the result. In this article, we will explore how to evaluate Boolean expressions using T-SQL.

Let’s start by understanding the procedure that can be used as a checking tool for evaluating Boolean expressions with up to 4 variables (A, B, C, D). The procedure, called “evaluate”, takes a Boolean expression string as input, along with the number of parameters in the equation and the values for each parameter separated by a dot sign.

The evaluation process involves replacing each NOT with the ~ sign, each AND with the & sign, and each OR with the | 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 is then evaluated using dynamic T-SQL.

Here is an example of the “evaluate” procedure:

CREATE PROCEDURE 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 INTO #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

Now, let’s see how we can use the “evaluate” procedure. We can pass the Boolean expression as a string, along with the number of variables and their corresponding values. Here are a couple of examples:

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 of the evaluation are as follows:

• The first expression is evaluated to TRUE.
• The second expression is evaluated to FALSE.

Let’s break down the evaluation process for the first expression:

(NOT (FALSE) AND FALSE) OR (TRUE AND NOT (FALSE))

This reduces to:

(TRUE AND FALSE) OR (TRUE AND TRUE)

Which becomes:

FALSE OR TRUE

And finally evaluates to TRUE.

On the other hand, the second expression becomes:

(NOT (TRUE) AND FALSE) OR (FALSE AND NOT (FALSE))

Which simplifies to:

(FALSE AND FALSE) OR (FALSE AND TRUE)

And further simplifies to:

FALSE OR FALSE

And ultimately evaluates to FALSE.

Understanding Boolean expressions and how to evaluate them is crucial for writing efficient and effective SQL Server queries. By using the “evaluate” procedure, you can easily check the validity of complex Boolean expressions in your code.

About the author:

Eli Leiba is a Senior Application DBA at Israel Electric Company, specializing in Oracle and MS SQL Server. He holds certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. With over 13 years of experience in the field, Eli also teaches SQL Server DBA and Development courses at Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.