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.
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
Read more... Read less...
Given that commonly
1 = true and
0 = false, all you need to do is count the number of rows, and cast to a
Hence, your posted code only needs a
COUNT() function added:
SELECT CAST(COUNT(1) AS BIT) AS Expr1 FROM [User] WHERE (UserID = 20070022)
Use 'Exists' which returns either 0 or 1.
The query will be like:
SELECT EXISTS(SELECT * FROM USER WHERE UserID = 20070022)
select CAST(COUNT(*) AS BIT) FROM [User] WHERE (UserID = 20070022)
If count(*) = 0 returns false. If count(*) > 0 returns true.
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.