Select @Script returns NULL instead of @Script SQL

I’m having a bit of trouble with the following query:

DECLARE
    @Type TINYINT = NULL,
    @ErrorDescription VARCHAR(MAX) = NULL

SELECT @Type

DECLARE @Script VARCHAR(MAX) = ''

SET @Script =
    'DECLARE @Type TINYINT = ' + CAST(@Type AS VARCHAR(10)) +'
    DECLARE @ErrorDescription VARCHAR(MAX) = ' + CASE WHEN @Type = 1 THEN '''' + @ErrorDescription + '''' ELSE 'NULL' END 

SELECT @Script

My @Type will be one of three values: Null, 0, 1

Null – Begin Process
0 – Succeeded process
1 – Errored Process

For 0 I get the result:

For NULL and 1 I get NULL in the @Script.

I need NULL and 1 to return @Script‘s result like 0 did.

I checked if the conversion of @Type to Varchar(10) was the problem, but it has no effect.

I’ve removed the apostrophes but that either returned an error or same result.

I changed @ErrorDescription to be ‘This is an error’ and that returned the proper result for 1 but not NULL.

Thanks in advance!

I’ve searched the internet for questions like mine but I saw a jQuery question and questions asking how to get NULL as a result.

>Solution :

You can interchange NULL for unknown

When you concatenate unknown to a string, the result is also unknown (if you add an unknown quantity to a known quantity you now have an unknown quantity). You need to use IsNull()

try

+ CAST(isnull(@Type,'0') AS VARCHAR(10)) +