Блог

В приведенном ниже запросе @employee переменная содержит большее количество символов-около 57 000 символов. Я не включил в приведенный ниже запрос, так как это будет длинный текст.

Когда я уменьшаю значения @employee , запрос работает идеально. Когда значения увеличиваются, я получаю ошибку ниже.

Я получаю эту ошибку —

Обработчику запросов не удалось создать план запроса из-за подсказок, определенных в этом запросе. Повторно отправьте запрос без указания каких-либо подсказок и без использования SET FORCEPLAN.

 DECLARE @ConfigNo NVARCHAR(100),
        @month    NVARCHAR(100),
        @year     NVARCHAR(100),
        @company  NVARCHAR(100),
        @employee NVARCHAR(max)

SET @ConfigNo = '1'
SET @month = '10'
SET @year = '2021'
SET @company = '2'
SET @employee =    '613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643'

EXEC(
'select x.Name,x.Code,x.Id,x.FieldName,x.LabelName,ISNULL(Round(y.Match,2,0),0) Match,x.Amount,x.PrintOrder,1 Flag  from (select a.*,(select distinct ISNULL(value,0) from (select  T.X.value(''@Value'', ''nvarchar(128)'') as Value,      T.X.value(''@Name'', ''nvarchar(128)'') as ColName       from dbo.TableTran                  cross apply TransactionFieldDetails.nodes(''/PayDetails/Column'') as T(X)        where EmployeeId = a.Id and MONTH = '
 @month ' and year = ' @year 
') abc where ColName = a.FieldName) Amount  from ( select s.FieldName,s.LabelName,isnull(e.FName,'''')    '' ''   isnull(e.LName,'''') as Name,e.Code,e.Id,s.PrintOrder  from dbo.TablePrint s cross join dbo.TableDetail e  where ComponentType = 1 and e.Id in ('
 @employee ') and s.PayConNo = ' @ConfigNo ' and s.CompanyId = ' @company 
') a  ) x  left join  ( select a.*, (select distinct ISNULL(value,0) from (select  T.X.value(''@Value'', ''nvarchar(128)'') as Value,       T.X.value(''@Name'', ''nvarchar(128)'') as ColName         from dbo.TableTran                   cross apply TransactionFieldDetails.nodes(''/PayDetails/Column'') as T(X)         where EmployeeId = a.Id and MONTH = '
 @month ' and year = ' @year 
') abc where ColName = a.ComputeField) Match  from (  select s.ActualField,  s.ComputeField,e.FName,e.Id   from dbo.PaySlipMatching s cross join dbo.TableDetail e  where e.Id in ('
 @employee ')  and s.PayConNo = ' @ConfigNo '  and s.CompanyId = ' @company 
') a   ) y   on x.FieldName = y.ActualField and x.Id = y.Id   union all  select a.Name,a.Code,a.id,a.FieldName,a.LabelName,0 Match, (select distinct ISNULL(value,0) from (select  T.X.value(''@Value'', ''nvarchar(128)'') as Value,        T.X.value(''@Name'', ''nvarchar(128)'') as ColName         from dbo.TableTran                    cross apply TransactionFieldDetails.nodes(''/PayDetails/Column'') as T(X)         where EmployeeId = a.Id and MONTH = '
 @month ' and year = ' @year 
') abc where ColName = a.FieldName) Amount,a.PrintOrder, 0 Flag   from   ( select s.FieldName,s.LabelName,isnull(e.FName,'''')    '' ''   isnull(e.LName,'''') as Name,e.Code,e.Id,s.PrintOrder   from dbo.TablePrint s cross join dbo.TableDetail e   where ComponentType = 2 and e.Id in ('
 @employee ')  and s.PayConNo = ' @ConfigNo ' and s.CompanyId = ' @company 
'  )  a OPTION (FORCE ORDER)') 
 

Пожалуйста, помогите мне с любым решением или идеей.

Отформатированный запрос —

 SELECT x.NAME,
       x.code,
       x.id,
       x.fieldname,
       x.labelname,
       Isnull(Round(y.match, 2, 0), 0) Match,
       x.amount,
       x.printorder,
       1                               Flag
FROM   (SELECT a.*,
               (SELECT DISTINCT Isnull(value, 0)
                FROM   (SELECT t.x.value('@Value', 'nvarchar(128)') AS Value,
                               t.x.value('@Name', 'nvarchar(128)')  AS ColName
                        FROM   dbo.tabletrans
                               CROSS apply
transactionfielddetails.nodes('/PayDetails/Column')
AS T(x)
WHERE  employeeid = a.id
AND month = 10
AND year = 2021) abc
WHERE  colname = a.fieldname) Amount
FROM   (SELECT s.fieldname,
s.labelname,
Isnull(e.fname, '')   ' '   Isnull(e.lname, '') AS NAME,
e.code,
e.id,
s.printorder
FROM   dbo.tableprint s
CROSS JOIN dbo.tabledetail e
WHERE  componenttype = 1
AND e.id IN ( 25039, 25040, 25041, 25042,
      25043, 25044, 25045, 25452 )
AND s.payconno = 1
AND s.companyid = 2) a) x
LEFT JOIN (SELECT a.*,
(SELECT DISTINCT Isnull(value, 0)
FROM   (SELECT t.x.value('@Value', 'nvarchar(128)') AS Value,
          t.x.value('@Name', 'nvarchar(128)')  AS
          ColName
   FROM   dbo.tabletrans
          CROSS apply
transactionfielddetails.nodes('/PayDetails/Column')
AS T(x)
WHERE  employeeid = a.id
AND month = 10
AND year = 2021) abc
WHERE  colname = a.computefield) Match
FROM   (SELECT s.actualfield,
s.computefield,
e.fname,
e.id
FROM   dbo.payslipmatching s
CROSS JOIN dbo.tabledetail e
WHERE  e.id IN ( 25039, 25040, 25041, 25042,
25043, 25044, 25045, 25452 )
AND s.payconno = 1
AND s.companyid = 2) a) y
ON x.fieldname = y.actualfield
AND x.id = y.id
UNION ALL
SELECT a.NAME,
       a.code,
       a.id,
       a.fieldname,
       a.labelname,
       0                              Match,
       (SELECT DISTINCT Isnull(value, 0)
        FROM   (SELECT t.x.value('@Value', 'nvarchar(128)') AS Value,
                       t.x.value('@Name', 'nvarchar(128)')  AS ColName
                FROM   dbo.tabletrans
                       CROSS apply
                       transactionfielddetails.nodes('/PayDetails/Column')
                       AS T(x)
                WHERE  employeeid = a.id
                       AND month = 10
                       AND year = 2021) abc
        WHERE  colname = a.fieldname) Amount,
       a.printorder,
       0                              Flag
FROM   (SELECT s.fieldname,
               s.labelname,
               Isnull(e.fname, '')   ' '   Isnull(e.lname, '') AS NAME,
               e.code,
               e.id,
               s.printorder
        FROM   dbo.tableprint s
               CROSS JOIN dbo.tabledetail e
        WHERE  componenttype = 2
               AND e.id IN ( 25039, 25040, 25041, 25042,
                             25043, 25044, 25045, 25452 )
               AND s.payconno = 1
               AND s.companyid = 2) a
OPTION (force ORDER)