none
string concatenation RRS feed

  • Question

  • The Payments field in the below update statement always return null even though the update statement executed successully. 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


    Thursday, April 9, 2015 9:11 AM

Answers

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


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, April 9, 2015 9:17 AM

All replies

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


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, April 9, 2015 9:17 AM
  • Could be because Payments column has NULL value.

    You can check it by executing below and if it has NULL replace it to blank by using ISNULL.

    SELECT Payments
    FROM INVOICE
    WHERE InvNo = 'IZM5728'


    Cheers,
    Vaibhav Chaudhari
    [MCTS], [MCP]

    Thursday, April 9, 2015 9:20 AM
  • Thanks,  I used CONCAT function as below but it gives me 

    'CONCAT' is not a recognized built-in function name.

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

    Thursday, April 9, 2015 9:30 AM
  • Ok, then you may be in SQL Server 2008 R2 or previous version, right?

    Use the ISNULL() one, should work.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, April 9, 2015 9:33 AM
  • Thanks,  I used CONCAT function as below but it gives me 

    'CONCAT' is not a recognized built-in function name.

    Could you try below?

    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
    
    


    Cheers,
    Vaibhav Chaudhari
    [MCTS], [MCP]

    Thursday, April 9, 2015 9:36 AM
  • Excellent!  Yes I use Sql server 2008 r2. ISNULL version works fine , Thank you.
    Thursday, April 9, 2015 9:46 AM