Forum Discussion

Chris_Ellis_777's avatar
Chris_Ellis_777
Copper Contributor
Jun 24, 2024

T-SQL Bit Field in View

Hi,

 

I'm not sure if this is the proper place for a T-SQL question. If not, I'd appreciate a pointer to the correct forum.

 

I have a view, and I would like a field that shows whether or not a specific value is null.

 

If I use this 

"IsNull" = CASE WHEN MyValue IS NULL THEN 1 ELSE 0 END

it ends up with exactly what I would want. It's a non-nullable field, except it's an integer.

 

Now, if I wrap that entire CASE in either a CAST or CONVERT to BIT, the resulting bit field is nullable.

 

How do I tell SQL Server that the field is not nullable?

 

Thanks in advance for any help,

Chris

1 Reply

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 25, 2024

    Chris_Ellis_777 force "not nullable" with the ISNULL function.

    You can simply test it with

    SELECT *   -- Returns "int" / "not null"
FROM sys.dm_exec_describe_first_result_set  
    (N'SELECT CASE WHEN 1 = 1 THEN 1 ELSE 0 END AS Result', null, 0);

SELECT * -- Returns "bit" / "null"
FROM sys.dm_exec_describe_first_result_set  
    (N'SELECT CONVERT(bit, CASE WHEN 1 = 1 THEN 1 ELSE 0 END) AS Result', null, 0) ;  

SELECT * -- Returns "bit" / "not null"
FROM sys.dm_exec_describe_first_result_set  
    (N'SELECT ISNULL(CONVERT(bit, CASE WHEN 1 = 1 THEN 1 ELSE 0 END), 0) AS Result', null, 0) ;

Resources