Because for INT and DECIMAL the value for CHARACTER_MAXIMUM_LENGTH is a NULL value. Adding a NULL value to a string will result in a NULL value.

Change the CASE statement to:

, DATA_TYPE

+ CASE

WHEN CHARACTER_MAXIMUM_LENGTH IS NULL

THEN ''

WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = - 1

THEN '(Max)'

ELSE '(' + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'

END AS [Type]