Select @Script returns NULL instead of @Script SQL-CodePudding

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:

@Type
0

@Script
DECLARE @Type TINYINT = 0 DECLARE @ErrorDescription VARCHAR(MAX) = NULL

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

@Script
NULL

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.

CodePudding user response：

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))