SQLTeam.com | Weblogs | Forums

How to display empty when value of paramters is null?

I work on SQL server 2012 the value

DECLARE @Company_ID VARCHAR(450)='1006274'
DECLARE @PackageDrawingNumber VARCHAR(450)=NULL
DECLARE @PartOrderSuffix VARCHAR(450)='N/A'
DECLARE @CountOfPCBConnectedPin VARCHAR(450)='N/A'


DECLARE @DKDrawingNumber VARCHAR(MAX);
IF @PartOrderSuffix='N/A'
SET @PartOrderSuffix=''
IF @CountOfPCBConnectedPin='N/A'
SET @CountOfPCBConnectedPin=''

SET @DKDrawingNumber=isnull(@Company_ID,' ')+isnull('~ '+isnull(@PackageDrawingNumber,''),'') + isnull('~ '+@PartOrderSuffix,'')+isnull('~ '+@CountOfPCBConnectedPin,'')
SELECT @DKDrawingNumber

desired result
1006274
but it display wrong result as below
1006274~ ~ ~
i need when parameters is null not display ~
so how to do that

SET @DKDrawingNumber=isnull(@Company_ID,' ')+isnull('~ '+@PackageDrawingNumber,'') + isnull('~ '+NULLIF(@PartOrderSuffix,''),'')+
    isnull('~ '+NULLIF(@CountOfPCBConnectedPin,''),'')
1 Like
DECLARE @DKDrawingNumber VARCHAR(MAX);

Set @PartOrderSuffix = nullif(@PartOrderSuffix, 'N/A');
Set @CountOfPCBConnectedPin = nullif(@CountOfPCBConnectedPin, 'N/A');
Set @DKDrawingNumber = concat(@Company_ID, '~' + @PackageDrawingNumber, '~' + @PartOrderSuffix, '~' + @CountOfPCBConnectedPin);

Select @DKDrawingNumber;