CODEWITHSUNDEEP
sqlselectboolean
mrjimoy_05
mrjimoy_05

Reputation: 3568

Return Boolean Value on SQL Select Statement

How to return a boolean value on SQL Select Statement?

I tried this code:

SELECT CAST(1 AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)

And it only returns TRUE if the UserID exists on the table. I want it to return FALSE if the UserID doesn't exist on the table.

Upvotes: 220

Views: 517154

Answers (9)

Chamin Thilakarathne
Chamin Thilakarathne

Reputation: 447

DECLARE @isAvailable      BIT = 0;

IF EXISTS(SELECT 1  FROM [User] WHERE (UserID = 20070022))
BEGIN
 SET @isAvailable = 1
END

initially isAvailable boolean value is set to 0

Upvotes: 2

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2424

For those of you who are interested in getting the value adding a custom column name, this worked for me:

CAST(
    CASE WHEN EXISTS ( 
           SELECT * 
           FROM mytable 
           WHERE mytable.id = 1
    ) 
    THEN TRUE 
    ELSE FALSE 
    END AS bool) 
AS "nameOfMyColumn"

You can skip the double quotes from the column name in case you're not interested in keeping the case sensitivity of the name (in some clients).

I slightly tweaked @Chad's answer for this.

Upvotes: 4

Dean Leitersdorf
Dean Leitersdorf

Reputation: 1341

Notice another equivalent problem: Creating an SQL query that returns (1) if the condition is satisfied and an empty result otherwise. Notice that a solution to this problem is more general and can easily be used with the above answers to achieve the question that you asked. Since this problem is more general, I am proving its solution in addition to the beautiful solutions presented above to your problem.

SELECT DISTINCT 1 AS Expr1
FROM [User]
WHERE (UserID = 20070022)

Upvotes: 2

Ananthi
Ananthi

Reputation: 413

Use 'Exists' which returns either 0 or 1.

The query will be like:

SELECT EXISTS(SELECT * FROM USER WHERE UserID = 20070022)

Upvotes: 19

RandomUs1r
RandomUs1r

Reputation: 4190

I do it like this:

SELECT 1 FROM [dbo].[User] WHERE UserID = 20070022

Seeing as a boolean can never be null (at least in .NET), it should default to false or you can set it to that yourself if it's defaulting true. However 1 = true, so null = false, and no extra syntax.

Note: I use Dapper as my micro orm, I'd imagine ADO should work the same.

Upvotes: 8

G.Noulas
G.Noulas

Reputation: 462

select CAST(COUNT(*) AS BIT) FROM [User] WHERE (UserID = 20070022)

If count(*) = 0 returns false. If count(*) > 0 returns true.

Upvotes: 12

cableload
cableload

Reputation: 4375

Possibly something along these lines:

SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT)
FROM dummy WHERE id = 1;

http://sqlfiddle.com/#!3/5e555/1

Upvotes: 47

Stewart
Stewart

Reputation: 18302

Given that commonly 1 = true and 0 = false, all you need to do is count the number of rows, and cast to a boolean.

Hence, your posted code only needs a COUNT() function added:

SELECT CAST(COUNT(1) AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)

Upvotes: 28

Chad
Chad

Reputation: 7507

What you have there will return no row at all if the user doesn't exist. Here's what you need:

SELECT CASE WHEN EXISTS (
    SELECT *
    FROM [User]
    WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

Upvotes: 346

Related Questions