Query stored in NVARCHAR(MAX) does not fit

Question

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)

`Msg 102, Level 15, State 1, Line 205 Incorrect syntax near ','.` But the error changes when I add, remove spaces. When I try very hard to fit the code inside, it works. — SQLfreaq, Jan 28 '15 at 18:51
How are you executing the `@query`? sp_executesql? It sounds like you are trying to execute the result of `print @query` which won't work because of the limitations of the `print` statement. Try using `sp_executesql @query` — dotjoe, Jan 28 '15 at 19:01
@dotjoe I use `execute(@query)`. Is there a different way that will work? — SQLfreaq, Jan 28 '15 at 19:05
SQL Server 2012 Microsoft SQL Server Management Studio 11.0.3153.0 Microsoft Analysis Services Client Tools 11.0.3130.0 Microsoft Data Access Components (MDAC) 6.0.6002.18005 Microsoft MSXML 3.0 4.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 4.0.30319.18444 Operating System 6.0.6002 — SQLfreaq, Jan 28 '15 at 19:12
maybe try `sp_executesql @query` just to rule that out. Seems like you are hitting the 8000 bytes limit from "earlier versions" of sql server...I'd think 2012 would be fine and have the 2gb limit — dotjoe, Jan 28 '15 at 19:21
I tried `EXECUTE sp_executesql @query`, but it does not work. — SQLfreaq, Jan 28 '15 at 19:27
Try breaking it up into chunks each toa separate varaible and tehn joining them together. THat way you can print each chunk and likely see where teh problem is. — HLGEM, Jan 28 '15 at 19:35
http://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/ — Aaron Bertrand, Jan 28 '15 at 19:36
You could insert the data before the pivot into a temp table and then use the dynamic sql to pivot the temp table. Should make the @query string a lot smaller. — dotjoe, Jan 28 '15 at 19:56

score 5 · Answer 1 · answered Jan 28 '15 at 17:36

5

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

answered Jan 28 '15 at 17:36

Pரதீப்

91,748
19
131
172

But it does not execute! If I remove some parts of it (like one of the UNION ALL's), it will execute. So there IS a problem. – SQLfreaq Jan 28 '15 at 18:15
1

@SQLfreaq The `print` statement fails to print out full text, but `sp_executesql` will handle [up to 2gb of query string](https://msdn.microsoft.com/en-us/library/ms188001.aspx). If that does not happen, there is something else wrong. – GSerg Jan 28 '15 at 18:47

score 3 · Answer 2 · edited May 23 '17 at 12:20

@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.

score 1 · Accepted Answer · answered Jan 28 '15 at 19:00

1

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)

answered Jan 28 '15 at 19:00

Philip Kelley

39,426
11
57
92

Solved my problem, but would LOVE to learn the reason that SQL does this because it has taken me nearly a day to run the code. – SQLfreaq Jan 28 '15 at 23:05
@SQLfreaq This probably because you did not use [`N`s in your string literals](http://stackoverflow.com/q/10025032/11683) (you were supposed to, they must be `nvarchar`s) and that confused the server in some way during concatenation. Indeed with your original code `@query` stores truncated text which can be seen with `select for xml`, but if you add `N` before each string piece it will contain full query. – GSerg Feb 02 '15 at 09:18
1

@SQLfreaq I just realized [dotjoe](http://stackoverflow.com/users/40822/dotjoe) has already [answered](http://stackoverflow.com/a/28201880/11683) exactly that. The relevant portion of the [linked Martin Smith's answer](http://stackoverflow.com/a/12639972/11683) goes under *Datatypes of string literals* subheader - one of your string literals is bigger than 4000 characters but smaller than 8000, so when used without `N` it casts to `nvarchar(4000)` and causes truncation, but with `N` it casts to `nvarchar(max)` and does not truncate. You should really accept that answer instead. – GSerg Feb 02 '15 at 10:20

Query stored in NVARCHAR(MAX) does not fit

3 Answers3