How do I add a Calculated [bit] Data Type field to a SQL Table?-CodePudding

I would like to add a [bit] field that is calculated from a Boolean [int] field

SQL Table looks as follow:

CREATE TABLE [dbo].[AutoIndexBoolean](
    [RowID] [int] NOT NULL,
    [Boolean] [int] NULL,
    [BooleanCalc2] AS (case when [Boolean]=(0) then 'False' when [Boolean]=(1) then 'True'  end),

SQL Query to add column looks as follow:

ALTER TABLE [dbo].[AutoIndexBoolean]
ADD [BooleanBit] [bit] AS (CASE WHEN [Boolean]=(0) THEN '0' WHEN [Boolean]=(1) THEN '1' END)

As soon as I specify the DataType "[bit]", the AS turns into a Syntax error.

How do I add a Calculated [bit] field into my table?

Thanks in advance

CodePudding user response：

You need to CAST/CONVERT the value returned from the CASE expression. You could likely do this as short as the following:

ALTER TABLE dbo.AutoIndexBoolean 
    ADD BooleanBit AS TRY_CONVERT(bit,Boolean);

If your column Boolean can have other values that 1, or 0 then do something like this:

ALTER TABLE dbo.AutoIndexBoolean 
    ADD BooleanBit AS CONVERT(bit, CASE Boolean WHEN 0 THEN 0 WHEN 1 THEN 1 END);

Of course, the real solution would seem to be change your column Boolean to be a bit, and then you don't need a second column at all.