How Do I Add A Calculated [bit] Data Type Field To A SQL Table?
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
You need to
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
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.
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?