How to encode a bit literal in a sql query

Question

I have a view which unions multiple tables. One table has a bit column called VisibleToCustomer. One of the other tables does not have this, and I want to hard code it in the view. I can do this with strings:-

SELECT  'Fred' "VisibleToCustomer", ....

or even ints

SELECT  1 "VisibleToCustomer", ....

but how do I do it with a bit? I.e. I want to do something like

SELECT  true "VisibleToCustomer", ....

but obviously the above doesn't work!

Answer 1

You can use

 SELECT  'true' AS "VisibleToCustomer"

bit has higher data type precedence than varchar so this will cast correctly.

SELECT CAST(1 AS BIT) AS "VisibleToCustomer"
UNION ALL
SELECT 'false' 

Returns

+-------------------+
| VisibleToCustomer |
+-------------------+
|                 1 |
|                 0 |
+-------------------+