My dynamic sql statement gets cut off at 8000 characters even when declared as varchar(max)

Question

I declare it as:

declare @SQL2 varchar(max)=

I could be wrong, but when this is the last statement, code is fine:

blah blah blah
AvgRevenueBooked
INTO ##Confidence3
FROM ##Confidence2
ORDER BY OptimizationGroupID, ProductID'

EXEC(@SQL2)

but when I make it:

blah blah blah
AvgRevenueBooked
INTO ##Confidence3
FROM ##Confidence2
ORDER BY OptimizationGroupID, ProductID 

SELECT 
    C.ProductID,
    C.OptimizationGroupID,
    C.TreatmentOrdinal,
    --C.CalculatedFromText,
    COALESCE(C.PricingType, CT.CalculatedFromText) AS PricingType,
    C.CurrencyCode,
    C.Origin,
    C.Destination,'+
    case when @passangers='Y' then ' C.Passangersgroup' else '' end +
    case when @fareclass='Y' then ' C.Fareclass' else '' end +
    case when @ispriorbooking='Y' then ' C.IsPriorBooking' else '' end +'
INTO ##PricingTypes
FROM  @CalculatedFromText CT
LEFT JOIN ##Confidence2 C
    ON C.CalculatedFromText = CT.CalculatedFrom'

EXEC(@SQL2), it says there is an error right around C.ProductID, (in this case it says at the ',', but if I delete things from previous statement, the error gets moved up relative to how many characters I delete) which is right where the 8000 character mark is (when I print the @SQL2).

Shouldnt I be able to have unlimited # of characters (reasonably) with an exec statement?

You can't display strings more than 8000 characters using `PRINT`. Try using `SELECT`. — Felix Pamittan, Dec 18 '15 at 01:03
I realize that, exec without any printing still gives an error at the 8000 character spot. are you saying put a select(@SQL2) at the end? Doesnt seem to do to print out anything useful — Adam Sanders, Dec 18 '15 at 01:07
see this article: (SQL NVARCHAR and VARCHAR Limits)(http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits) — Thomas, Dec 18 '15 at 01:08
Use `SELECT @SQL2` to get the whole dynamic query. And then execute that query to pinpoint the error. — Felix Pamittan, Dec 18 '15 at 01:10
doh, dont know why I ddint see that one before, solved it...thanks! — Adam Sanders, Dec 18 '15 at 01:13

My dynamic sql statement gets cut off at 8000 characters even when declared as varchar(max)

0 Answers0