Post
Ask Question

How To Return Boolean Values From SQL Server

Introduction 

Here, I will tell you the possible ways to return calculated Boolean values from SQL Server code. In a lot of places, you want to progress conditionally. You send some input values to the stored procedure and want to get a Boolean value which decides the future flow of the application. If you are selecting Boolean values from the table in SQL stored procedure then that is pretty simple because you are just selecting a Boolean column. But what if you do things dynamically on the go? What if that Boolean column is not part of the table? So we will see those issues here.
 
CASE 1 - When Boolean Column is already there in the table and we need to return the same from the stored procedure
  1. CREATE TABLE [dbo].[Students](  
  2.     [StudentId] [int] NOT NULL,  
  3.     [StudentName] [nvarchar](50) NOT NULL,  
  4.     [CourseName] [nvarchar](50) NOT NULL,  
  5.     [IsEnrolled] [bit] NOT NULL  
  6. )   
Lets check what is in the table.
  1. SELECT *  FROM [dbo].[Students]  
Output comes as given below,
 
How To Return Boolean Values From SQL Server
 
So, the stored procedure will look like this.
  1. CREATE PROCEDURE [dbo].[usp_IsEnrolled]  
  2.     -- Add the parameters for the stored procedure here  
  3.       
  4.     @StudentId INT  
  5. AS  
  6. BEGIN  
  7. Select IsEnrolled from dbo.Students Where StudentId=@StudentId  
  8. END  
  9. GO  
The result of this stored procedures, when running with parameter @StudentId = 2, comes like this.

How To Return Boolean Values From SQL Server 
 
So, this was pretty easy for you as the column you are selecting is itself boolean.
 
Understanding
 
In the procedure written above, we are trying to find out whether the student is enrolled or not. So as we tried to check for Rahul it gives the value as 0 equivalent to False. So the field which we are selecting is itself a BIT hence it becomes very easy to get the boolean value in this manner.
 
CASE 2 - Let us return a calculated boolean from SQL stored procedure based on some condition.
 
Let us check if the student is from B.Tech course by the following procedure.
  1. CREATE PROCEDURE [dbo].[usp_IsBTechCandidate]  
  2.     -- Add the parameters for the stored procedure here  
  3.       
  4.     @StudentId INT  
  5. AS  
  6. BEGIN  
  7. DECLARE @IsBTech BIT  
  8. IF EXISTS(SELECT * FROM dbo.Students Where StudentId=@StudentId and  CourseName='B.Tech')  
  9. BEGIN  
  10. SET @IsBTech=1  
  11. END  
  12. ELSE  
  13. BEGIN  
  14. SET @IsBTech=0  
  15. END  
  16. SELECT @IsBTech AS 'IsBTech'  
  17. END  
This procedure, when executed with StudentId=1, gives the following result with return type Boolean only.
 
 
Understanding
 
This case is a little different from above in the sense that we do not actually have the property stored in the table which we are looking for. So, here we are actually calculating the BIT Value. We are trying to find out whether the student is enrolled for B.Tech course. Hence when we executed CASE 2 procedure with parameter StudentId = 1 for student Kamal in the table we get a True return value. This is also directly coming as BIT because we have declared it as a BIT and then based on the condition we are assigning a value.
 
CASE 3 - Achieving whatever done in case 2 without declaring a bit variable
 
So, we can rewrite the above procedure as below.
  1. CREATE PROCEDURE [dbo].[usp_IsBTechCandidate]  
  2.     -- Add the parameters for the stored procedure here  
  3.       
  4.     @StudentId INT  
  5. AS  
  6. BEGIN  
  7. IF EXISTS(SELECT * FROM dbo.Students Where StudentId=@StudentId and  CourseName='B.Tech')  
  8. BEGIN  
  9. SELECT CAST(1 AS BIT) AS 'IsBTech'  
  10. END  
  11. ELSE  
  12. BEGIN  
  13. SELECT CAST(0 AS BIT) AS 'IsBTech'  
  14. END  
  15. END   
Here, we have used CAST function to return a bit. Based on this value we can take the flow of our application further. Output of the above procedure comes as below when StudentId=1
 
How To Return Boolean Values From SQL Server 
 
Understanding
 
In case 3, we have the same problem statement like we are trying to find out whether the student is a B.Tech candidate or not. But the implementation is a bit different -- here we have not declared a BIT variable. We need to directly return the calculated BIT value based on condition. So we use the CAST() method available in SQL. We can use the CAST method to cast from one data type to another. You might want to cast a float value to int value for that we use CAST(). So in case three is based on the condition we are casting int 1 to BIT which returns True value and int 0 to BIT which returns False value.
 
Hence there are multiple ways by which we can return Boolean values from stored procedures. The main ways we have described above.
 
Summary 
 
So, you have seen above that if you have declared a variable in BIT datatype in stored procedure declaration, then you will, by default, get its value in Boolean(C#) / BIT data type but if you are not using BIT variable, then you will have to cast that int value to the BIT data type as seen in CASE 3. In this process, anything which is non zero is assumed TRUE. So, for values like -2, -1, 1, 2 and so when you will get true value and for 0, you will get False.
 
In this way, we can implement the scenario using any of CASE 1, CASE 2 and CASE 3 methodologies based on our need. Write in the comments if you have some issues while performing these operations.


Similar Articles