Die in NVARCHAR (MAX) gespeicherte Abfrage passt nicht

Ich habe folgende Abfrage:

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 ' 

Wenn ich diese Abfrage mit print @query ich folgendes, mit dem letzten Teil meines Code abgeschnitten:

 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 

Wie kann ich alle meine Abfrage in @ query passen, damit ich es richtig ausführen kann? Anmerkung: Die statement von NoDisplayName, dass die Abfrage unabhängig funktionieren würde, ist nicht wahr, wie ich es versucht habe. Ich habe alle unnötigen Räume entfernt und meinen Code beschnitten (bei gleichzeitiger Reduzierung der functionalität), und es funktioniert. Also ein path, um den Code zu @query passen wird geschätzt!

Vielen Dank!


Wenn ich den Code in zwei Teile trenne, läuft die Abfrage problemlos ab:

 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) 

Ich habe das schon mal getroffen (wenn auch mit varchar (max)). Unten ist die Arbeit, mit der wir zusammenkamen. Dies ist eine der Situationen, in denen ich sorta verstehen kann, warum SQL das tut, aber ich kann es nicht wirklich erklären, ganz zu schweigen von genau, warum es so funktioniert. (Google um genügend und du wirst wahrscheinlich eine technische Erklärung finden, wenn du die richtigen Suchbegriffe herausfinden kannst.)

Angenommen, diese variables:

 DECLARE @Query AS NVARCHAR(MAX) ,@Cols nvarchar(10) SET @Cols = 'A, B, C, D, E' 

Setzen Sie Ihren Befehl wie folgt:

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

Die folgenden prüft die Ergebnisse (len <> datalength, wenn du mit nvarchars arbeitet)

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

Von MSDN

Eine Nachrichtenzeichenfolge kann bis zu 8.000 character lang sein, wenn es sich um eine Nicht-Unicode-characterfolge handelt und 4.000 character lang, wenn es sich um eine Unicode-characterfolge handelt. Längere Saiten sind abgeschnitten. Die datatypen varchar (max) und nvarchar (max) werden auf datatypen gekürzt, die nicht größer sind als varchar (8000) und nvarchar (4000)

Obwohl der printing nicht Ihre gesamte Abfrage zeigt, wird Query ausgeführt

@Martin Smith hat einen Link zur Antwort gepostet … Es ist wegen der Trunkierung, wenn du die SQL-Strings mit der @cols Variable verkettet @cols . Wenn du deine Saiten änderst, um das N Präfix zu benutzen, werden sie als Nvarchen behandelt und ohne Trunkierung verkettet. Der Grund, den es in der Probe, die Sie zur Verfügung gestellt haben, ist, dass @query2 verhindert, dass die Trunkierung auftritt, da Sie sie bis zu Größen weniger als 4000 character brach.