CODEWITHSUNDEEP
sql-server-2005intnvarchar
BigBlack
BigBlack

Reputation: 163

SQL Server 2005: help to concatenate Nvarchar and Int

I have this block of code with an error because I'm using an Int variable inside a Nvarchar. 

DECLARE @FattAnnoCorrente INT;
DECLARE @Tabscontianno1 NVARCHAR(MAX);

SET @Tabscontianno1 = 
N'<p align="left"><b>ANNO ' + @Anno1 + ' - </b><b>' + @FattAnnoCorrente + '<br>
  </b></p>
<table height="62" border="1" cellpadding="2" cellspacing="2"
  width="501">
  <tbody>
    <tr>
      <td valign="top">FATTURATO<br>
      </td>
      <td valign="top">SCONTO<br>
      </td>
    </tr>' + CAST ((
                    SELECT  
                    td = SUM(TOTNETTORIGA),    '',
                    td = SCONTIESTESI
                    FROM .dbo.TESTEDOCUMENTI 
                    INNER JOIN .dbo.RIGHEDOCUMENTI
                    ON PROGRESSIVO=IDTESTA AND TOTNETTORIGA <>'0'
                    WHERE  CODCLIFOR = @CodiceCliente AND .dbo.TESTEDOCUMENTI.DOCCHIUSO = '0' AND .dbo.TESTEDOCUMENTI.BLOCCATO = '0' AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC'  AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(GETDATE())
                    GROUP BY TESTEDOCUMENTI.ESERCIZIO,SCONTIESTESI
    FOR XML PATH('tr'), TYPE )
    AS NVARCHAR(MAX) ) +
    N'  </tbody>
</table>'+
    N'<BR/>' ;

I get this error:

Conversion failed when converting the nvarchar value 'ANNO 2016 - ' to data type int.

If I use

CAST(CAST(COALESCE(@FattAnnoCorrente) as int) as varchar(255))

I instead get these errors:

Msg 102, Level 15, State 1, Line 154
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 173
Incorrect syntax near the keyword 'FOR'.

Can you help me to solve this problem.

After insert it in the nvarchar variable I need to format it as money like that: 

'€ ' + REPLACE(CONVERT(varchar, CAST(@FattAnnoCorrente AS money), 105),',','.')

Thank you guys!

Upvotes: 0

Views: 310

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

You will need to cast all INT types to VARCHAR..

in your case you are first casting them to INT..

change below statement

CAST(CAST(COALESCE(@FattAnnoCorrente) as int) as varchar(255))

to

cast(COALESCE(@FattAnnoCorrente,'somevalue') as varchar(255))

Upvotes: 1

Related Questions