3

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)

shows the 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".

2
  • The IIF result CAN be null - soIIF result data type MUST be nullable.
    – Akina
    May 11, 2018 at 9:59
  • Why are you using variables if the parameters to IIF is "non-null constants". Could you not just use the constants in the function instead? May 14, 2018 at 6:57

1 Answer 1

6

First off, the column is_nullable returned from sys.dm_exec_describe_first_result_set_for_object has the following definition as per docs.Microsoft.com:

Contains the value 1 if the column allows NULLs, 0 if the column does not allow NULLs, and 1 if it cannot be determined that the column allows NULLs.

This means that the engine could also be looking for a situation where it can't predict the nullability of the return column. (This is more for your information than anything else - in this case it doesn't appear to be happening)

Now when we simplify the stored procedure and eliminate the variables as such:

CREATE PROCEDURE TestProc
AS

SELECT IIF('hello' = 'hello','true','false')
GO

We can see the nullability goes away.

This is because the variables you are utilising in your procedure (@Zerobit and @Onebit) are NULLABLE by definition. The variables themselves are getting set at run-time, but the underlying metadata for the data type of the variable is still nullable.

There is also a possibility that the query optimiser needs to account for all scenarios, and it can't use the value of the variable at runtime for an all-use plan.

If we rewrite the query and do not set the value of the variables, we will see the same results (The column metadata for the return of the procedure is still NULLABLE):

CREATE PROCEDURE TestProc
AS

DECLARE @trueoutput VARCHAR(10)
DECLARE @falseoutput VARCHAR(10)

SELECT IIF('hello' = 'hello',@trueoutput,@falseoutput)
GO

My suggestion for the above case would be (as Mikael Eriksson says) to use constants - this is unless it's a cut down example and this isn't possible. The issue then would be the returned data type would always be nullable without being wrapped with ISNULL, CASE etc

2
  • Thanks George, I have updated my question slightly - initially I was going to ask two separate questions, but now I feel it's more useful to keep them together.
    – Ian Kemp
    May 14, 2018 at 5:42
  • @IanKemp I've updated my answer - unfortunately I think most directions have already been covered. May 14, 2018 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.