CODEWITHSUNDEEP
sqlsql-serverstored-proceduresnvarchar
SQLfreaq
SQLfreaq

Reputation: 141

Query stored in NVARCHAR(MAX) does not fit

I have the following query:

DECLARE @query AS NVARCHAR(MAX);
SET @query ='

SELECT

col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
'+ @cols +'

FROM

(

(

SELECT
''LEASİNG'' [col1],
 d.REGNR [col2],
 cl.DEFINITION_ [col3],
 '''' [col4],
 d.DESCRIPTION [col5],
 c.PAYMENTDATE [col6],
 a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
 c.TRCURR [TRCURR],
 e.CURCODE [CURCODE]


FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI

FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a

LEFT OUTER JOIN

(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF

INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF


INNER JOIN

LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF

INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF

INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF

INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF

INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)


WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0

)

UNION ALL

(

SELECT
''ÇEK'',
cs.NEWSERINO,
bn.DEFINITION_,
ban.DEFINITION_,
cl.DEFINITION_,
cs.DUEDATE,
cs.AMOUNT,
cs.TRCURR,
cur.CURCODE

FROM

LG_011_01_CSTRANS a

INNER JOIN

(
SELECT 
CSREF,
MAX(STATNO) [STATNO]

FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO

INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF

WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11

)

UNION ALL

(

SELECT
CASE WHEN cl.SPECODE=''OTOMATİK'' THEN ''OTOMATİK ÖDEME'' WHEN cl.SPECODE=''ZORUNLU'' THEN ''ZORUNLU CARİ'' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'''',
'''',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE

FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)

WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE ''320%'' AND cl.SPECODE IN (''OTOMATİK'',''ZORUNLU'') AND cur.FIRMNR=11

)

UNION ALL

(
SELECT
 CASE d.SPECODE WHEN '''' THEN ''KREDİ'' WHEN ''FORWARD'' THEN ''FORWARD'' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE

 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT

        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF

LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 1 
        GROUP BY PARENTREF) c 
ON b.LOGICALREF = c.PARENTREF

INNER JOIN LG_011_BNCREDITCARD d
ON b.CREDITREF=d.LOGICALREF

INNER JOIN L_CURRENCYLIST e
ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)

INNER JOIN LG_011_BNCARD f
ON d.BNCRREF=f.LOGICALREF

INNER JOIN LG_011_BANKACC g
ON d.BNACCREF=g.LOGICALREF

WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)<>0
)

) x

PIVOT

(

SUM(AMOUNT)
FOR CURCODE IN ('+ @cols +')

) xx

ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
'

When I print this query using print @query, I get the following, with the last part of my code cut off:

SELECT

col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
[TL],[USD],[EUR]

FROM

(

(

SELECT
'LEASİNG' [col1],
 d.REGNR [col2],
 cl.DEFINITION_ [col3],
 '' [col4],
 d.DESCRIPTION [col5],
 c.PAYMENTDATE [col6],
 a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
 c.TRCURR [TRCURR],
 e.CURCODE [CURCODE]


FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI

FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a

LEFT OUTER JOIN

(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF

INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF


INNER JOIN

LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF

INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF

INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF

INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF

INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)


WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0

)

UNION ALL

(

SELECT
'ÇEK',
cs.NEWSERINO,
bn.DEFINITION_,
ban.DEFINITION_,
cl.DEFINITION_,
cs.DUEDATE,
cs.AMOUNT,
cs.TRCURR,
cur.CURCODE

FROM

LG_011_01_CSTRANS a

INNER JOIN

(
SELECT 
CSREF,
MAX(STATNO) [STATNO]

FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO

INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF

WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11

)

UNION ALL

(

SELECT
CASE WHEN cl.SPECODE='OTOMATİK' THEN 'OTOMATİK ÖDEME' WHEN cl.SPECODE='ZORUNLU' THEN 'ZORUNLU CARİ' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'',
'',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE

FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)

WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU') AND cur.FIRMNR=11

)

UNION ALL

(
SELECT
 CASE d.SPECODE WHEN '' THEN 'KREDİ' WHEN 'FORWARD' THEN 'FORWARD' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE

 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT

        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF

LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR

How can I fit all my query inside @query, so that I can execute it properly? Note: NoDisplayName's statement that the query would work regardless is not true as I have tried it. I have removed all the unnecessary spaces and trimmed my code (while reducing the functionality), and it works. So a way to fit the code to @query is appreciated!

Thanks!

When I separate the code into two parts, the query executes without any problems:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @query2 AS NVARCHAR(MAX);

SET @cols= STUFF((SELECT ','+QUOTENAME(c.CURCODE) FROM (

(

SELECT DISTINCT b.CURCODE,a.TRCURR FROM LG_011_BNCREDITCARD a INNER JOIN L_CURRENCYLIST b 
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)

)

UNION

(

SELECT DISTINCT b.CURCODE,a.TRCURR

FROM LG_011_LEASINGPAYMENTSLNS a

INNER JOIN LG_011_PURCHOFFER z
ON a.LEASINGREF=z.LEASINGREF

INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF
FROM LG_011_PURCHOFFER
GROUP BY LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF

INNER JOIN L_CURRENCYLIST b
ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)

WHERE z.STATUS=4

)

UNION

(

SELECT DISTINCT cur.CURCODE,cs.TRCURR FROM 

LG_011_01_CSTRANS a

INNER JOIN

(
SELECT 
CSREF,
MAX(STATNO) [STATNO]

FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO

INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)

WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11

)

UNION

(

SELECT DISTINCT cur.CURCODE, pt.TRCURR

FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)

WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU')

)

) c ORDER BY c.TRCURR FOR XML PATH(''), TYPE 
).value('.','NVARCHAR(MAX)'),1,1,'')


SET @query ='

SELECT

col1 [TÜR],
col2 [KOD],
col3 [BANKA/CARİ],
col4 [BANKA HESABI],
col5 [AÇIKLAMA],
col6 [VADE],
'+ @cols +'

FROM

(

(

SELECT
''LEASİNG'' [col1],
 d.REGNR [col2],
 cl.DEFINITION_ [col3],
 '''' [col4],
 d.DESCRIPTION [col5],
 c.PAYMENTDATE [col6],
 a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
 c.TRCURR [TRCURR],
 e.CURCODE [CURCODE]


FROM
(SELECT
LOGICALREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI

FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=0
GROUP BY LOGICALREF) a

LEFT OUTER JOIN

(SELECT
PARENTREF,
SUM(PAYMENTTOTAL) AS KDVSIZ,
SUM(INTTOTAL) AS FAIZ,
SUM(MAINTOTAL) AS ANAPARA,
SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
SUM(VATINPAYMENTTOTAL) AS KDVLI
FROM LG_011_LEASINGPAYMENTSLNS
WHERE TRANSTYPE=1
GROUP BY PARENTREF
) b 
ON a.LOGICALREF= b.PARENTREF

INNER JOIN
LG_011_LEASINGPAYMENTSLNS c
ON a.LOGICALREF=c.LOGICALREF


INNER JOIN

LG_011_LEASINGREG d
ON c.LEASINGREF=d.LOGICALREF

INNER JOIN
LG_011_PURCHOFFER z
ON c.LEASINGREF=z.LEASINGREF

INNER JOIN
(SELECT 
MAX(LOGICALREF) LOGICALREF,
LEASINGREF,
CLIENTREF
FROM LG_011_PURCHOFFER
GROUP BY CLIENTREF,LEASINGREF) y
ON z.LOGICALREF=y.LOGICALREF

INNER JOIN LG_011_CLCARD cl
ON z.CLIENTREF=cl.LOGICALREF

INNER JOIN L_CURRENCYLIST e
ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)


WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)<>0

)

UNION ALL

(

SELECT
''ÇEK'',
cs.NEWSERINO,
bn.DEFINITION_,
ban.DEFINITION_,
cl.DEFINITION_,
cs.DUEDATE,
cs.AMOUNT,
cs.TRCURR,
cur.CURCODE

FROM

LG_011_01_CSTRANS a

INNER JOIN

(
SELECT 
CSREF,
MAX(STATNO) [STATNO]

FROM LG_011_01_CSTRANS
GROUP BY CSREF) b
ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO

INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF

WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11

)

UNION ALL

(

SELECT
CASE WHEN cl.SPECODE=''OTOMATİK'' THEN ''OTOMATİK ÖDEME'' WHEN cl.SPECODE=''ZORUNLU'' THEN ''ZORUNLU CARİ'' END,
CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE<>14 THEN clf.TRANNO ELSE inv.FICHENO END,
cl.DEFINITION_,
'''',
'''',
pt.DATE_,
pt.TOTAL,
pt.TRCURR,
cur.CURCODE

FROM LG_011_01_PAYTRANS pt
INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)

WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE ''320%'' AND cl.SPECODE IN (''OTOMATİK'',''ZORUNLU'') AND cur.FIRMNR=11
'



SET @query2='

)

UNION ALL

(
SELECT
 CASE d.SPECODE WHEN '''' THEN ''KREDİ'' WHEN ''FORWARD'' THEN ''FORWARD'' END [TÜR],
 d.CODE,
 f.DEFINITION_,
 g.DEFINITION_,
 d.NAME_,
 b.DUEDATE,
 a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
 d.TRCURR,
 e.CURCODE

 FROM
    (SELECT 
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT

        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 0 
        GROUP BY PARENTREF) a 
INNER JOIN (SELECT
 LOGICALREF,
 PARENTREF,
 CREDITREF,
 DUEDATE,
 OPRDATE
 FROM LG_011_BNCREPAYTR
                WHERE TRANSTYPE = 0) b
ON a.PARENTREF=b.PARENTREF

LEFT OUTER JOIN (SELECT
 PARENTREF,
 SUM(TOTAL) AS ANAPARA,
 SUM(INTTOTAL) AS FAIZ,
 SUM(BSMVTOTAL) AS BSMV,
 SUM(KKDFTOTAL) AS KKDF,
 SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
        FROM LG_011_BNCREPAYTR 
        WHERE TRANSTYPE = 1 
        GROUP BY PARENTREF) c 
ON b.LOGICALREF = c.PARENTREF

INNER JOIN LG_011_BNCREDITCARD d
ON b.CREDITREF=d.LOGICALREF

INNER JOIN L_CURRENCYLIST e
ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)

INNER JOIN LG_011_BNCARD f
ON d.BNCRREF=f.LOGICALREF

INNER JOIN LG_011_BANKACC g
ON d.BNACCREF=g.LOGICALREF

WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)<>0
)

) x

PIVOT

(

SUM(AMOUNT)
FOR CURCODE IN ('+ @cols +')

) xx

ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
'

EXECUTE(@query + @query2)

Upvotes: 0

Views: 1397

Answers (3)

dotjoe
dotjoe

Reputation: 26940

@Martin Smith posted a link to the answer... It's due to truncation when you are concatenating the sql strings with the @cols variable. If you change your strings to use the N prefix they will be treated as nvarchars and concatenate without truncation. The reason it works in the sample you provided is that @query2 prevented the truncation from occurring since you broke them up to sizes less than 4000 chars.

Upvotes: 3

Philip Kelley
Philip Kelley

Reputation: 40319

I have hit this before (albeit with varchar(max)). Below is the work-around we came up with. This is one of those situations where I can sorta understand why SQL does this, but I can't really explain it, let alone spell out precisely why it works this way. (Google around enough and you'll probably find a technical explanation, if you can figure out the proper search terms.)

Assuming these variables:

DECLARE
  @Query AS NVARCHAR(MAX)
 ,@Cols nvarchar(10)

SET @Cols = 'A, B, C, D, E'

Set your command as follows:

SET @Query = replace( cast('' as varchar(max))
                       + 'Big chunk of text containing <@Cols> wherever you need to substitute in that string'
                     ,'<@Cols>'
                     ,@Cols)

The following checks the results (len <> datalength when you're working with nvarchars)

PRINT @Query
PRINT len(@query)
PRINT datalength(@query)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

From MSDN

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000)

Even though the print doesn't show your entire query, Query will execute

Upvotes: 5

Related Questions