title: SQL variable declared as Varchar(8000) trims content at 5160
body: <p>I have declared <code>SQL1</code> variable of data type <code>varchar(max)</code> with max length of <code>8000</code>. I am expecting it to hold upto <code>8000</code> chars, however in my scenario even though the string length is <code>5160</code>, still the variable is not holding the entire query.</p>
  <pre class="lang-sql prettyprint-override"><code>DECLARE @SQL1 VARCHAR (8000)
  --DECLARE @SQL2 VARCHAR (8000)
  SET @SQL1=''
  SET @SQL1= 
  '
  UPDATE v
  SET v.ValueLoc = (sf.ValueLoc*isnull(sf.FTE,1)*-1), v.FTE = sf.FTE, v.CurrencyLOC = sf.Currency, v.K4_MODIFIED = getDate()
  FROM ##STAFF_PLAN_VALUES v 
  INNER JOIN (
      SELECT 
          dat.Employee_ID, dat.ElementId, dat.SeriesId, dat.Year, dat.Month, dat.Version, dat.Currency, dat.SeriesValidFrom, dat.SeriesValidTo,
          convert(numeric(18,2),sum(dat.MonSalRatio)) AS ValueLOC, 
          convert(numeric(18,2),sum(dat.MonFTE)) AS FTE
      FROM (
          SELECT
              pt3.Employee_ID, pt3.SeriesId, pt3.ElementId, pt3.Year, pt3.Month, pt4.MonSalary, pt4.MonFTE, pt3.Version`enter code here`, pt4.Currency, 
              pt3.MonthStart, pt3.MonthEnd, pt4.SeriesValidFrom, pt4.SeriesValidTo, pt4.FteSalFrom, pt4.FteSalTo,
              (CASE WHEN pt3.MonthStart&gt;pt4.FteSalFrom THEN pt3.MonthStart ELSE pt4.FteSalFrom END) AS ValidFrom,
              (CASE WHEN pt3.MonthEnd&lt;pt4.FteSalTo THEN pt3.MonthEnd ELSE pt4.FteSalTo END) AS ValidTo,
              cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart&gt;pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd&lt;pt4.SeriesValidTo THEN pt3.MonthEnd ELSE pt4.SeriesValidTo END))+1) AS float)/
              cast((DATEDIFF(day,pt3.MonthStart,pt3.MonthEnd)+1) AS float)*pt4.MonSalary AS MonSalRatio,
              cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart&gt;pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd&lt;pt4.SeriesValidTo THEN pt3.MonthEnd ELSE pt4.SeriesValidTo END))+1) AS float)/
              cast((DATEDIFF(day,pt3.MonthStart,pt3.MonthEnd)+1) AS float)*pt4.MonFTE AS MonFTERatio
          FROM (
                  SELECT v.*,
                      cast(cast(v.Year AS varchar(4)) + ''-'' +  right(''0'' + cast(v.Month AS varchar(2)),2) + ''-01'' AS date) AS MonthStart,
                      dateadd(day,-1,dateadd(month,1,cast(cast(v.Year AS varchar(4)) + ''-'' +  right(''0'' + cast(v.Month AS varchar(2)),2) + ''-01'' AS date))) AS MonthEnd
                  FROM ##STAFF_PLAN_VALUES AS v
              ) AS pt3 INNER JOIN (
                  SELECT
                      pt1.Employee_ID, pt1.SeriesID, pt1.ElementId,
                      pt1.ValidFrom AS SeriesValidFrom,
                      pt1.ValidTo AS SeriesValidTo,
                      pt2.StartDate AS FteSalFrom,
                      pt2.EndDate AS FteSalTo,
                      pt1.Version, pt1.PLCode, pt1.LegalEntityId,pt1.BusinessUnitId,pt1.DepartmentId,pt1.Currency,pt2.MonFTE,pt2.MonSalary
                      FROM
                      ##STAFF_PLAN_ELEMENTS AS pt1
  INNER JOIN (SELECT C.Employee_ID, C.StartDate, C.Salary, 0 AS FTE, convert(numeric(18,2),C.Salary/12) AS MonSalary, 0 AS MonFTE, ''1SAL'' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##salseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate FROM ##salseq AS C 
  UNION SELECT C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS MonFTE, ''1SAL'' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##fteseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##fteseq AS C UNION SELECT 
  C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS MonFTE, ''2SOC'' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##fteseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##fteseq AS C
  UNION SELECT C.Employee_ID, C.StartDate, C.Travel, NULL AS FTE, convert(numeric(18,2),C.Travel/12) AS MonSalary, NULL AS MonFTE, ''3TRA'' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##traseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##traseq AS C
  UNION
  SELECT 
  C.Employee_ID, C.StartDate, C.Pension, NULL AS FTE, convert(numeric(18,2),C.Pension/12) AS MonSalary, NULL AS MonFTE, ''4PPL'' AS ElementId,
                              isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##pplseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
                          FROM ##pplseq AS C
                          UNION
                          SELECT 
                              C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS MonFTE, ''4PPL'' AS ElementId,
                              isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##fteseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
                          FROM ##fteseq AS C
                          UNION
                          SELECT 
                              C.Employee_ID, C.StartDate, C.Other, NULL AS FTE, convert(numeric(18,2),C.Other/12) AS MonSalary, NULL AS MonFTE, ''5OTH'' AS ElementId,
                              isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##othseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
                          FROM ##othseq AS C
                      ) AS pt2 ON pt1.Employee_ID=pt2.Employee_ID AND pt1.ElementId=pt2.ElementId AND pt1.ValidFrom &lt;= pt2.EndDate AND pt1.ValidTo &gt;= pt2.StartDate
              ) AS pt4 ON pt3.Employee_ID=pt4.Employee_ID AND pt3.ElementId=pt4.ElementId AND pt3.SeriesID=pt4.SeriesID AND pt3.Year = ''' +@year+''' AND pt3.Version = pt4.Version 
              AND pt3.MonthStart &lt;= pt4.FteSalTo AND pt3.MonthEnd &gt;= pt4.FteSalFrom
              AND pt3.MonthStart &lt;= pt4.SeriesValidTo AND pt3.MonthEnd &gt;= pt4.SeriesValidFrom
          ) AS dat
      GROUP BY dat.Employee_ID, dat.ElementId, dat.SeriesId, dat.Year, dat.Month, dat.Version, dat.Currency, dat.SeriesValidFrom, dat.SeriesValidTo
  ) AS sf 
  ON v.Employee_ID=sf.Employee_ID AND v.ElementId=sf.ElementId AND v.SeriesId=sf.SeriesId AND v.Year=sf.Year AND v.Version=sf.Version AND v.Month=sf.Month
  '
  
  print @sql1
  </code></pre>
  <p><strong>Result :</strong> As we can result , its not taking complete string</p>
  <pre class="lang-sql prettyprint-override"><code>UPDATE v
  SET v.ValueLoc = (sf.ValueLoc*isnull(sf.FTE,1)*-1), v.FTE = sf.FTE, v.CurrencyLOC = sf.Currency, v.K4_MODIFIED = getDate()
  FROM ##STAFF_PLAN_VALUES v 
  INNER JOIN (
      SELECT 
          dat.Employee_ID, dat.ElementId, dat.SeriesId, dat.Year, dat.Month, dat.Version, dat.Currency, dat.SeriesValidFrom, dat.SeriesValidTo,
          convert(numeric(18,2),sum(dat.MonSalRatio)) AS ValueLOC, 
          convert(numeric(18,2),sum(dat.MonFTE)) AS FTE
      FROM (
          SELECT
              pt3.Employee_ID, pt3.SeriesId, pt3.ElementId, pt3.Year, pt3.Month, pt4.MonSalary, pt4.MonFTE, pt3.Version, pt4.Currency, 
              pt3.MonthStart, pt3.MonthEnd, pt4.SeriesValidFrom, pt4.SeriesValidTo, pt4.FteSalFrom, pt4.FteSalTo,
              (CASE WHEN pt3.MonthStart&gt;pt4.FteSalFrom THEN pt3.MonthStart ELSE pt4.FteSalFrom END) AS ValidFrom,
              (CASE WHEN pt3.MonthEnd&lt;pt4.FteSalTo THEN pt3.MonthEnd ELSE pt4.FteSalTo END) AS ValidTo,
              cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart&gt;pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd&lt;pt4.SeriesValidTo THEN pt3.MonthEnd ELSE pt4.SeriesValidTo END))+1) AS float)/
              cast((DATEDIFF(day,pt3.MonthStart,pt3.MonthEnd)+1) AS float)*pt4.MonSalary AS MonSalRatio,
              cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart&gt;pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd&lt;pt4.SeriesValidTo THEN pt3.MonthEnd ELSE pt4.SeriesValidTo END))+1) AS float)/
              cast((DATEDIFF(day,pt3.MonthStart,pt3.MonthEnd)+1) AS float)*pt4.MonFTE AS MonFTERatio
          FROM (
                  SELECT v.*,
                      cast(cast(v.Year AS varchar(4)) + '-' +  right('0' + cast(v.Month AS varchar(2)),2) + '-01' AS date) AS MonthStart,
                      dateadd(day,-1,dateadd(month,1,cast(cast(v.Year AS varchar(4)) + '-' +  right('0' + cast(v.Month AS varchar(2)),2) + '-01' AS date))) AS MonthEnd
                  FROM ##STAFF_PLAN_VALUES AS v
              ) AS pt3 INNER JOIN (
                  SELECT
                      pt1.Employee_ID, pt1.SeriesID, pt1.ElementId,
                      pt1.ValidFrom AS SeriesValidFrom,
                      pt1.ValidTo AS SeriesValidTo,
                      pt2.StartDate AS FteSalFrom,
                      pt2.EndDate AS FteSalTo,
                      pt1.Version, pt1.PLCode, pt1.LegalEntityId,pt1.BusinessUnitId,pt1.DepartmentId,pt1.Currency,pt2.MonFTE,pt2.MonSalary
                      FROM
                      ##STAFF_PLAN_ELEMENTS AS pt1
  INNER JOIN (SELECT C.Employee_ID, C.StartDate, C.Salary, 0 AS FTE, convert(numeric(18,2),C.Salary/12) AS MonSalary, 0 AS MonFTE, '1SAL' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##salseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate FROM ##salseq AS C 
  UNION SELECT C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS MonFTE, '1SAL' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##fteseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##fteseq AS C UNION SELECT 
  C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS MonFTE, '2SOC' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##fteseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##fteseq AS C
  UNION SELECT C.Employee_ID, C.StartDate, C.Travel, NULL AS FTE, convert(numeric(18,2),C.Travel/12) AS MonSalary, NULL AS MonFTE, '3TRA' AS ElementId,
  isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##traseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
  FROM ##traseq AS C
  UNION
  SELECT 
  C.Employee_ID, C.StartDate, C.Pension, NULL AS FTE, convert(numeric(18,2),C.Pension/12) AS MonSalary, NULL AS MonFTE, '4PPL' AS ElementId,
                              isnull((SELECT max(dateadd(day,-1,D.StartDate)) FROM ##pplseq AS D WHERE C.Employee_ID=D.Employee_ID AND C.SeqID=D.SeqID-1),C.FinalEndDate) AS EndDate 
                          FROM ##pplseq AS C
                          UNION
                          SELECT 
                              C.Employee_ID, C.StartDate, 0 AS Salary, C.FTE, 0 AS MonSalary, convert(numeric(18,2),C.FTE) AS Mo
  </code></pre>
  <p>Any help is appreciated.</p>
  <p><strong>SQL Server Version</strong> :
  Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)
  Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)</p>