SQL variable declared as Varchar(8000) trims content at 5160

Question

I have declared SQL1 variable of data type varchar(max) with max length of 8000. I am expecting it to hold upto 8000 chars, however in my scenario even though the string length is 5160, still the variable is not holding the entire query.

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>pt4.FteSalFrom THEN pt3.MonthStart ELSE pt4.FteSalFrom END) AS ValidFrom,
            (CASE WHEN pt3.MonthEnd<pt4.FteSalTo THEN pt3.MonthEnd ELSE pt4.FteSalTo END) AS ValidTo,
            cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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 <= pt2.EndDate AND pt1.ValidTo >= 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 <= pt4.FteSalTo AND pt3.MonthEnd >= pt4.FteSalFrom
            AND pt3.MonthStart <= pt4.SeriesValidTo AND pt3.MonthEnd >= 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

Result : As we can result , its not taking complete string

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>pt4.FteSalFrom THEN pt3.MonthStart ELSE pt4.FteSalFrom END) AS ValidFrom,
            (CASE WHEN pt3.MonthEnd<pt4.FteSalTo THEN pt3.MonthEnd ELSE pt4.FteSalTo END) AS ValidTo,
            cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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

Any help is appreciated.

SQL Server Version : 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)

You're validating that the string is not the right length my measuring the output of a PRINT command? What is SELECT DATALENGTH(@sql1);? For some other ideas see Validate the contents of large dynamic SQL strings in SQL Server. — Aaron Bertrand
Im not used to working with Microsoft SQL, but as far as i know the "lenght" is not number of characters, but the size in bytes. Some characters takes up more space than others docs.microsoft.com/en-us/sql/t-sql/data-types/… — mortendhansen
Not reproducible. I 've changed AND pt3.Year = ''' +@year+''' AND to AND pt3.Year = '+''' +@year'''+' AND. The data length is 5894 — Ilyes
Also @sql1 should be nvarchar and if you need more than 4000 characters you should use max. If you execute dynamic SQL correctly (e.g. by passing in strongly-typed parameters to avoid SQL injection), you would use sp_executesql, which requires nvarchar. See this, this, and this. — Aaron Bertrand
@Inc nvarchar(max) length is 2 GB. If the string might be longer than 4000 characters, they should use nvarchar(max). If they have a SQL string that exceeds 2 GB, I think they have a bigger problem than a data type choice. — Aaron Bertrand

FFFffff FFFffff · Accepted Answer · 2020-10-06T20:15:09

The real issue here is not the datatype, bud the print() command in T-SQL, which has a limit of 4000 characters for NVARCHAR and 8000 characters for VARCHAR data types. This goes back to some old SQL Server when NVARCHAR(MAX) was equivalent with NVARCHAR(4000). They changed the (MAX) meaning, but not the print() function.

The easiest solution is to avoid printing it. If you try to insert or execute the code, it will work as expected.

For the cutoff characters, a quick guide is something like:

NVARCHAR (4000) will cut off the string at 4000th character.
VARCHAR (8000) will cut off the string at 8000th character.
NVARCHAR (MAX) and VARCHAR (MAX) are simply large enough for any common string (~2GB).

Here is reproducible example of both cutoff values and the print() issue:

DECLARE @SQL_varchar_8 VARCHAR (8000);
DECLARE @SQL_varchar_max VARCHAR (MAX);
DECLARE @SQL_nvarchar_8 NVARCHAR (4000);
DECLARE @SQL_nvarchar_max NVARCHAR (MAX);
declare @year int = 2020;
SET @SQL_varchar_8= 
'
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>pt4.FteSalFrom THEN pt3.MonthStart ELSE pt4.FteSalFrom END) AS ValidFrom,
            (CASE WHEN pt3.MonthEnd<pt4.FteSalTo THEN pt3.MonthEnd ELSE pt4.FteSalTo END) AS ValidTo,
            cast((DATEDIFF(day,(CASE WHEN pt3.MonthStart>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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>pt4.SeriesValidFrom THEN pt3.MonthStart ELSE pt4.SeriesValidFrom END),(CASE WHEN pt3.MonthEnd<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 <= pt2.EndDate AND pt1.ValidTo >= pt2.StartDate
            ) AS pt4 ON pt3.Employee_ID=pt4.Employee_ID AND pt3.ElementId=pt4.ElementId AND pt3.SeriesID=pt4.SeriesID AND pt3.Year = ''' + cast(@year as varchar) +''' AND pt3.Version = pt4.Version 
            AND pt3.MonthStart <= pt4.FteSalTo AND pt3.MonthEnd >= pt4.FteSalFrom
            AND pt3.MonthStart <= pt4.SeriesValidTo AND pt3.MonthEnd >= 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
';

/*a bit of cheating to make it shorter*/
set @SQL_varchar_max = @SQL_varchar_8
set @SQL_nvarchar_8 = @SQL_varchar_8
set @SQL_nvarchar_max = @SQL_varchar_8

select len(@SQL_varchar_8)    -- 5959
    , len(@SQL_varchar_max)   -- 5959
    , len(@SQL_nvarchar_8)    -- 3998
    , len(@SQL_nvarchar_max); -- 5959

print(@SQL_nvarchar_max);
print('----------');
print(right(@SQL_nvarchar_max, 4000)); -- prints text that was cutoff in previous print()

SQL variable declared as Varchar(8000) trims content at 5160

1 Answers