T-SQL Bit Field in View

[Chris_Ellis_777]

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

[#2]

@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) ;