Unpredictable behaviour in nested CASE statement

I’m unable to figure out why the control goes always to a statement irrespective of inside CASE condition.

A normal SQL statement works, but with my table it does not work.

--Not working--

SELECT
CASE WHEN [INTERNALDESCRIPTION] IS NOT NULL THEN --INTERNALDESCRIPTION IS A TEXT FIELD
    CASE WHEN 'INT' = 'INT' THEN  -- Or 'TEXT' = 'INT'
        REPLACE( CONVERT(VARCHAR(MAX),[INTERNALDESCRIPTION]  ) ,'''','') --should have come here
    ELSE 
        REPLACE( CONVERT(INT,[INTERNALDESCRIPTION]  ) ,'''',''  ) -- Always comes here no matter what condition
    END 
ELSE 
    'NULL' 
END 
FROM  DBO.RESOURCESTRINGMASTER WITH(NOLOCK)  WHERE 1=1

-------working--
DECLARE @VALUE1 AS varchar(max) = '1Test', @VALUE2 AS VARCHAR(MAX) =   '2'
SELECT 
CASE WHEN @VALUE1 IS NOT NULL THEN 
    CASE WHEN 'INT' = 'INT' THEN        
        REPLACE( CONVERT(VARCHAR(MAX),@VALUE1  ) ,'''','')
    ELSE 
        REPLACE( CONVERT(INT,@VALUE2  ) ,'''',''  ) 
    END 
ELSE 
    'NULL' 
END 

And results in below error:

Explicit conversion from data type text to int is not allowed.

Answer

Explicit conversion from data type text to int is not allowed.

This error message seems pretty clear. Why are you using a text data type? It is deprecated. To quote from the documentation:

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

So, your code on the real table is executing the ELSE condition, which causes it to fail. In the code with constants, ELSE condition is not failing. Why is this?

I think the error is being caught in the compilation phase of the query. The error does not occur in the second example, because SQL Server is short-circuiting the query, recognizing that the ELSE is not needed. The code in the second example is simply not compiled.

I am pretty sure you would see the same behavior if you replaced the code with 1 / 0 (although the other part of the case expression would need to change as well for the types to be compatible).