EXEC in T-SQL ignores CONCAT_NULL_YIELDS_NULL

Question

With any version of Sql Server, I can’t understand why in the next script EXEC returns the result of GetDate (). I did not expect a result.

SET CONCAT_NULL_YIELDS_NULL ON; DECLARE @sql_select nvarchar(150) = 'SELECT GetDate()'; DECLARE @sql_select2 nvarchar(150) = NULL; SELECT @sql_select + @sql_select2 EXEC(@sql_select + @sql_select2) 

Answer 1

I think inside exec , NULL converted to an empty string. Here is a simpler example

 DECLARE @sql_select2 varchar(200) ; exec('select 1' + @sql_select2) 

returns 1 , although it is associated with a NULL value.

But when we do concatenation outside of it, it works as expected.

 DECLARE @sql_select NVARCHAR(150) = 'SELECT GetDate()'; DECLARE @sql_select2 NVARCHAR(150) = NULL; DECLARE @sql VARCHAR(500) SET @sql = @sql_select + @sql_select2 EXEC(@sql) 

returns nothing