By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,462 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,462 IT Pros & Developers. It's quick & easy.

How to Return Boolean TRUE/FALSE Values

NeoPa
Expert Mod 15k+
P: 31,476
I am trying, with a spectacular lack of success, to return TRUE and FALSE values in my SELECT statement. This SQL (below) is simply illustrative. It doesn't reflect my real world query. I would just like to know how to return such values in T-SQL.
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,TRUE AS [CostMissing]
  3.  
  4. FROM   [DBManagement].[dbo].[vwCustStock]
  5.  
  6. WHERE  ([PR_RANGE] In(70,91))
  7.  
  8. GO
  9.  
  10. Msg 207, Level 16, State 1, Line 2
  11. Invalid column name 'TRUE'.
For an idea, should anyone want it, why I need this please see SELECT Assertion.
Jun 30 '10 #1
Share this Question
Share on Google+
8 Replies


P: 11
hi neopa!
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,'TRUE' AS [CostMissing]
  3.  
  4. FROM   [DBManagement].[dbo].[vwCustStock]
  5.  
  6. WHERE  ([PR_RANGE] In(70,91))
use single quotes for true as its not an integer value
Jun 30 '10 #2

NeoPa
Expert Mod 15k+
P: 31,476
Thanks for your suggestion. However, adding the quotes forces the value to be a string, which is not what I'm after. I've renamed the thread to make this point clearer. I'm after returning Boolean values explicitly. I know how to return them as the result of a function call, and various other ways, but I'd like to know how to return a specific boolean value.

I do appreciate your attempting to help anyway :)
Jun 30 '10 #3

Banfa
Expert Mod 5K+
P: 8,916
Since Transact-SQL has no boolean data type (see Data Types (Transact-SQL)) it is reasonable that it doesn't support constant values of that type (i.e. true and false).

I suggest you return 0 (false) and 1 (true).
Jun 30 '10 #4

NeoPa
Expert Mod 15k+
P: 31,476
Thanks Banfa.

There's some sense in that, but as it seems to support the Bit datatype, which mentions values of True and False, as well as undefined, would it not be reasonable to expect explicit values of True and False to be available? It makes reading the SQL and making sense of it so much easier after all. I know you have no power to change how it's set up, but I'd be interested to read your thinking in response to this point.
Jun 30 '10 #5

Banfa
Expert Mod 5K+
P: 8,916
Actually that page says

An integer data type that can take a value of 1, 0, or NULL.
It doesn't say it can take the values true and false but rather that strings with the value 'TRUE' and 'FALSE' can be converted to a bit field. I believe this will happen implicitly in the right context but otherwise an explicit cast/convert is required.

The contexts I have seen in your threads are not ones where the field is already of type bit and therefore you would need to explicitly cast/convert something like

cast('TRUE' as bit)

I think. I can't test this as I don't currently have an SQL Server installed.
Jun 30 '10 #6

NeoPa
Expert Mod 15k+
P: 31,476
Thanks Banfa. I must admit that their use of TRUE and FALSE favlues, rather than 'TRUE' and 'FALSE' as you have it and I would expect from string values, threw me right off the scent. Notwithstanding their reference to String values of ....

It would seem then, strangely in my view, that SQL Server doesn't support the concept of predefined values for TRUE and FALSE. Thanks for helping to get there in the end.
Jun 30 '10 #7

ck9663
Expert 2.5K+
P: 2,878
This cast('TRUE' as bit) will work.

Actually when you view the table using Enterprise Manager (Open Table), bit data type columns will show True/False, not 1/0. Editing the value, though, still requires you input 1 or 0.

Happy Coding!!!

~~ CK`
Jun 30 '10 #8

NeoPa
Expert Mod 15k+
P: 31,476
Thanks CK. At least I now think I know where I stand with all this :)
Jun 30 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.