How to Return Boolean TRUE/FALSE Values

[NeoPa]

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.

SELECT [PR_PRODUCT]
      ,TRUE AS [CostMissing]
 
FROM   [DBManagement].[dbo].[vwCustStock]
 
WHERE  ([PR_RANGE] In(70,91))
 
GO
 
Msg 207, Level 16, State 1, Line 2
Invalid column name 'TRUE'.

For an idea, should anyone want it, why I need this please see SELECT Assertion.

[romiverma]

hi neopa!
Try this

SELECT [PR_PRODUCT]
      ,'TRUE' AS [CostMissing]
 
FROM   [DBManagement].[dbo].[vwCustStock]
 
WHERE  ([PR_RANGE] In(70,91))

use single quotes for true as its not an integer value

[NeoPa]

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 :)

[Banfa]

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).

[NeoPa]

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.

[Banfa]

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.

[NeoPa]

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.

[ck9663]

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`

[NeoPa]

Thanks CK. At least I now think I know where I stand with all this :)