SQL with Manoj

–> Question:

The Payments field in the below update statement always return null even though the update statement executed successfully.

What is wrong here?

DECLARE @Temp nvarchar(200), @sql nvarchar(max),@Details_InvNo nvarchar(4000), @Details_Amt nvarchar(100),@ChkNo nvarchar(100)
set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = convert(nvarchar(max),Payments) + cast(@Temp as nvarchar(max)), 
AmtDue = 100, 
AmtPaid=0 
WHERE InvNo = @Details_InvNo
GO

–>My Response:

As the Payment columns would be NULL and you are concatenating it with another String, the result will be NULL only. So, just put an IsNULL() over Payments column after “=” operator, like this:

DECLARE @Temp nvarchar(200), 
	@sql nvarchar(max), 
	@Details_InvNo nvarchar(4000), 
	@Details_Amt nvarchar(100),
	@ChkNo nvarchar(100)

set @Details_InvNo = 'IZM5728'
set @ChkNo = '1-33333333'
set @Details_Amt = '100'
SET @Temp = ',[' + convert(nvarchar(max),@ChkNo) + '=' + cast(@Details_Amt as nvarchar(50)) + ']' 

UPDATE INVOICE 
SET Payments = ISNULL(convert(nvarchar(max),Payments),'') + cast(@Temp as nvarchar(max)), 
	AmtDue = 100, 
	AmtPaid=0   
WHERE InvNo = @Details_InvNo
go

Or use CONCAT() function like this:

SET Payments = CONCAT(convert(nvarchar(max),Payments), cast(@Temp as nvarchar(max))), 

Ref Link.