Consider the following sproc:
create or alter procedure TestSproc as begin declare @ZeroBit bit = 0, @OneBit bit = 1 -- Table.Id is a nullable integer column select iif(Table.Id is null, @ZeroBit, @OneBit) as ColumnNotNull end
Once it's been compiled, running the following:
select name, system_type_name, is_nullable from sys.dm_exec_describe_first_result_set_for_object(object_id('TestSproc'), 0)
ColumnNotNull column correctly typed as a
bit, but that it is nullable.
The nullability does not make sense to me, since the arguments I passed to
iif are non-null constants, so why is the resultant column nullable? And is it possible to get it to be non-nullable without wrapping the expression in
isnull to get around this, which to my mind is - or rather, should be - unnecessary?
The documentation for
iif makes no mention of nullability, merely stating that this function "Returns the data type with the highest precedence from the types in true_value and false_value".