Блог

Главная - Вопросы по программированию - Переменная SQL, объявленная как Varchar(8000), ограничивает содержимое на 5160

Переменная SQL, объявленная как Varchar(8000), ограничивает содержимое на 5160

#sql #sql-server #tsql #sql-server-2017

#sql #sql-server #tsql #sql-server-2017

Вопрос:

Я объявил SQL1 переменную типа данных varchar(max) с максимальной длиной 8000 . Я ожидаю, что она будет содержать до 8000 символов, однако в моем сценарии, несмотря на то, что длина строки 5160 , все равно переменная не содержит весь запрос.

 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

Результат: Как мы можем видеть, она не принимает полную строку

 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

Любая помощь приветствуется.

Версия SQL Server: Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) — 14.0.3257.3 (X64) Standard Edition (64-разрядная версия) на Windows Server 2016 Datacenter 10.0 (сборка 14393: ) (гипервизор)

Комментарии:

1. Вы подтверждаете, что строка не соответствует длине, которую я измеряю при выводе команды ПЕЧАТИ? Что такое SELECT DATALENGTH(@sql1); ? Для некоторых других идей см. Проверка содержимого больших динамических строк SQL в SQL Server .

2. Я не привык работать с Microsoft SQL, но, насколько я знаю, «длина» — это не количество символов, а размер в байтах. Некоторые символы занимают больше места, чем другие learn.microsoft.com/en-us/sql/t-sql/data-types /…

3. Не воспроизводимо . Я изменил AND pt3.Year = ''' @year ''' AND на AND pt3.Year = ' ''' @year''' ' AND . Длина данных равна 5894

4. Также @sql1 должно быть nvarchar , и если вам нужно более 4000 символов, вы должны использовать max . Если вы правильно выполняете динамический SQL (например, передавая строго типизированные параметры, чтобы избежать внедрения SQL), вы бы использовали sp_executesql , что требует nvarchar . Смотрите это , это и это .

5. @Inc nvarchar(max) длина составляет 2 ГБ. Если строка может быть длиннее 4000 символов, они должны использовать nvarchar(max) . Если у них есть строка SQL, которая превышает 2 ГБ, я думаю, что у них проблема посерьезнее, чем выбор типа данных.

Ответ №1:

Реальная проблема здесь не в типе данных, а в print() команде в T-SQL, которая имеет ограничение в 4000 символов для NVARCHAR и 8000 символов для типов данных VARCHAR. Это восходит к какому-то старому SQL Server, когда NVARCHAR (MAX) был эквивалентен NVARCHAR (4000). Они изменили (МАКСИМАЛЬНОЕ) значение, но не print() функцию.

Самое простое решение — не печатать его. Если вы попытаетесь вставить или выполнить код, он будет работать так, как ожидалось.

Для символов отсечения краткое руководство выглядит примерно так:

  • NVARCHAR (4000) будет обрезана строка на 4000-м символе.
  • VARCHAR (8000) будет обрезана строка на 8000-м символе.
  • NVARCHAR (MAX) и VARCHAR (MAX) просто достаточно велики для любой общей строки (~ 2 ГБ).

Вот воспроизводимый пример обоих значений ограничения и print() проблемы:

 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()