samedi 20 août 2016

Sql 2005: Help to concatenate Nvarchar and Int

I've 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've 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've instead those 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!

Aucun commentaire:

Enregistrer un commentaire