Daniel Calbimonte
sql boolean in sql query

SQL Boolean Tutorial

November 28, 2022 by

The SQL Boolean data type is not included in SQL Server. Other databases like Oracle and MySQL include the Boolean data type that accepts the values of TRUE, and FALSE.

SQL Server uses the bit data type that stores 0, 1, and NULL values that can be used instead of the TRUE, FALSE, and NULL values. In this tutorial, we will teach several examples to select and insert values.

What is the bit data type?

It is a data type to store 3 values. 1, 0, and NULL values. This data type is more efficient than the SQL Boolean data type used by other DBMSs because it only uses 1 bit to store the data. Let’s take a look at an example of the usage.

SQL Boolean examples in SQL Server

The following example will create a variable named myBoolean with the bit data type. We will then set the variable to true and finally show the results.

bit data type equal to 1

Note that when we select the value it shows the value 1 which means that is true.

In the same way, you can set the Boolean variable to false.

bit data type equal to 0

Alternatively, and as a best practice, you can set numbers to the variable. The following example will set the myBoolean variable to 1 instead of a true value. This is a best practice.

Bit data type equal to 1

For a false value, you will set the value to 0.

bit data type equal to false

To resume, you can set the bit values with 0, 1 or use ‘TRUE’ and ‘FALSE instead’.

Finally, you can set a bit variable with NULL values if necessary. The following example will set the myBoolean bit variable to null.

null values with bit data type

How to replace 0, 1 values with true or false values

As you saw before, the bit shows 0 and 1 instead of TRUE and FALSE. How can I replace the 0 and 1 values with TRUE and FALSE?

The following example illustrates how to do that. We will use the case statement to replace the values. The following example shows how to do it.

sql boolean in variable

The code does the following. If the value is 1, then it will return TRUE. If the value of the variable is 0, it will return false. Otherwise, it will return a Null value.

How to create a table with a bit data type column

The following example will create a table named myBooleanTable with 2 columns. An ID column with the integer data type and the ispair column with a bit data type.

How to insert data with SQL Boolean data type

To insert data with a bit data type, you can only use the 0,1 and NULL values. For this example, we are inserting data in the myBooleanTable previously created.

Working with SQL queries

The following example will use the famous Adventureworks database. If you do not have the Adventureworks database installed, please follow our article related.

For this example, we will use the HumanResource.Employee table which contains some bit data type columns. For this example, we use the SalariedFlag bit column.

The following example will show the BusinessEntityID, NationalIDNumber, and SalariedFlag columns where the SalariedFlag is 1.

The salary displayed is the following.

sql boolean in sql query

To get the same information for employees with a Salariedflag equal to false, you can change the salariedFlag value to false (0).

Alternatively, you can use the TRUE and FALSE values instead of the 0 and 1 values, but this practice is not recommended.

Finally, we have the NULL value in the where clause. The Null value is slightly different than the 0, and 1 values because you have to use the IS operator instead of the equal.

The following example illustrates that.

CONVERT SQL boolean to other data types

The following example shows what happens if we want to add a bit data type to a numeric value. We will declare the variable myBoolean and set it to true and then add 2.

convert bit to integer

So, in the example, @myBoolean is 1, and 1+2 is equal to 3 which means that bit it is implicitly treated as a numeric value.

If we try to concatenate with the + operator, we will have an error. The following example illustrates this problem.

The error message is as follows:

The data types varchar and bit are incompatible in the add operator.

The best solution for this problem is to use the CONCAT operator which will convert the bit variable to a string.

CONCAT will concatenate the string with the bit variable and show the following result.

convert bit to string with concat

Alternatively, but not recommended, we can use the CONVERT function to convert the bit variable into a string. For this scenario, the CONCAT is the best solution. However, for other scenarios, you may not be able to use CAST and may need to use CONVERT instead.

The convert value is converting the @myBoolean bit data type into varchar(1) which can be concatenated with the string.

The other option is to use CAST. CAST is similar to CONVERT and we are only including this option because you can find code from someone else and it is better to be familiar with this function as well.

The syntaxis for CAST is slightly different than cast. You have to specify the expression and then the data type.

Stored procedures with SQL Boolean variables

Stored procedures can also be used in SQL Server stored procedures. The stored procedures are code stored in the database that you can call and reuse multiple times.

In this example, we will show how to create a stored procedure with bit variables and execute it.

We will first create a stored procedure named salariedFlag that shows the login ID of users with the salariedFlag equal to true (1) or false (0). According to the variable @salariedFlag, it shows the values specified in the where clause. The values will be retrieved from the Employee table.

To execute the stored procedure, we will invoke it using the Exec clause and specifying a value for the @salariedFlag variable.

Exec is executing the salariedFlag and the @salaried is receiving the value of 1 (TRUE).

The result displayed by the stored procedure is the following:

sql boolean in store procedure

These login IDs are the Login IDs with the SalariedFlag equal to TRUE. If we want to see the Login IDs with the value equal to False (0), you can invoke the stored procedure. We can use the exec command or the execute command. We can also include the name of the parameter (which is a best practice to make it easier to understand and easier to maintain especially when multiple parameters are used in the stored procedure).

As you can see in the example, we are using the execute command as an alternative to the exec command and we are invoking the @salariedFlag whereas we did not include that in our previous example. Finally, we are using the value equal to a False string instead of using the 0 value (not recommended, but it works).

The value displayed by this stored procedure execution is the following.

store procedure with bit equal to false

Summary

SQL Server does not include the SQL Boolean data type like other databases, but you can use the SQL Boolean which is the same. The main difference is that instead of using TRUE and FALSE values, you use 1 and 0. This is more efficient because it requires just a single bit to store values. In this article, we showed how to use this bit variable as a variable in a SQL query. We also show how to convert bit data to integer and string and finally, we teach how to use the query inside a stored procedure. If you have more questions about this SQL Boolean data type do not hesitate to write your comments.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

7,413 Views