语法不正确.使用with语句时

Question

有一个错误的存储过程：

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'zzsSrListOrdinace')
      DROP PROCEDURE dbo.zzsSrListOrdinace

IF NOT EXISTS(SELECT * FROM sysobjects WHERE name = 'zzsSrListOrdinace')
BEGIN
    EXEC dbo.sp_executesql N'create PROCEDURE [dbo].[zzsSrListOrdinace]
             @r NVARCHAR(MAX),
             @ic NVARCHAR(MAX) = NULL,
             @nz NVARCHAR(MAX) = NULL,
             @icz NVARCHAR(MAX) = NULL,
             @icp NVARCHAR(MAX) = NULL,
             @odb NVARCHAR(MAX) = NULL,
             @id BIGINT = NULL,
             @cs NVARCHAR(MAX) = NULL,
             @ts BIGINT = NULL,
             @pod DATETIME = NULL,
             @pdo DATETIME = NULL,
             @uod DATETIME = NULL,
             @udo DATETIME = NULL,
             @sp NVARCHAR(MAX) = NULL,
             @e2 NVARCHAR(MAX) = NULL,
             @tz NVARCHAR(MAX) = NULL,
             @sv NVARCHAR(MAX) = NULL,
             @vyk NVARCHAR(MAX) = NULL,
             @rl NVARCHAR(MAX) = NULL,
             @prl BIT = NULL,
             @s INT = NULL,
             @o NVARCHAR(MAX) = NULL,
             @p INT,
             @totalCount INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON
        DECLARE @UkonceniTyp NVARCHAR(6) = N''POZAST'', @Query NVARCHAR(MAX), @orderByClause NVARCHAR(MAX)

        CREATE TABLE #Results 
        (
            OrdinaceId BIGINT,
            CleneniPzsId BIGINT,
            PoskytovatelZsId BIGINT,
            Ico NVARCHAR(255),
            Icz NVARCHAR(255),
            Zkratka NVARCHAR(255),
            Icp NVARCHAR(255),
            OdbornostKod NVARCHAR(255),
            OrdinaceNazev NVARCHAR(255),
            OrdinaceAdresaCela NVARCHAR(255),
            Priloha2Id BIGINT,
            Smluvni BIT,
            EP2 NVARCHAR(255),
            StavPzp NVARCHAR(255),
            Status NVARCHAR(255),
            Info INT,
            PlatnostOd DATETIME,
            PlatnostDo DATETIME
       )

    DECLARE @ColumnSort TABLE (
        SortColumn nvarchar(255),
        OrderColumn nvarchar(255)
    )
    INSERT INTO @ColumnSort exec getOrderByClauseTable @Sort=@r, @Order=@o

    INSERT into #Results 

    with Pril2Formular_max_per_id as (
        select ord.Id as OrdinaceId, max(pr.Priloha2Id) as max_Priloha2Id
        from Ordinace ord
          left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
          left join Pril2Formular pr on pr.CleneniPzsId = cle.Id
        group by ord.Id
    )
    select 
    distinct ord.Id as OrdinaceId,
    ord.CleneniPzsId,
      posk.Id,
      posk.ICO as Ico,
      zar.ICZ as Icz,
      posk.NazevZkraceny as Zkratka,
      case when cle.Primariat = 0 then cle.Icp end as Icp,
      IIF(cle.Primariat = 1, pri.OborKod , cle.OdbornostKod) as OdbornostKod,
      ord.Nazev as OrdinaceNazev,
      (select Street + N'' '' + DescriptiveNo + N'', '' + PostCode +  N'' '' + City from ICIS_Repl.repl.Address where Code = ord.NavAddressCode and Type = N''ORDINACE'' and (ValidFrom is null or ValidFrom <= GETDATE()) and (ValidTill is null or ValidTill >= GETDATE() or ValidTill = N''1753-01-01 00:00:00'')) as OrdinaceAdresaCela,
      pril.Id as Priloha2Id,
      cle.Smluvni,
      cisP2KomunikaceStav.Nazev as EP2,
      cisPzs.Nazev as StavPzp,
      pril.Status,
      pril.Info,
      pril.PriPlatnostOd as PlatnostOd,
      pril.PriPlatnostDo as PlatnostDo
    from Ordinace ord
      left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
      left join ZarizeniPZS zar on cle.ZarizeniPzsId = zar.Id
      left join PoskytovatelZS posk on zar.PoskytovatelZsId = posk.Id
      left join Primariat pri on cle.PrimariatId = pri.Id
      left join Pril2Formular_max_per_id pr on pr.OrdinaceId = ord.Id
      left join Priloha2 pril on pril.Id = pr.max_Priloha2Id and (pril.PriPlatnostOd <= GETDATE() or pril.PriPlatnostOd is null) and (pril.PriPlatnostDo is null or pril.PriPlatnostDo >= GETDATE()) 
      join CisCiselnik cisP2KomunikaceStav on posk.P2KomunikaceStavKod = cisP2KomunikaceStav.Kod AND cisP2KomunikaceStav.Oblast = N''P2KomunikaceStav'' and cisP2KomunikaceStav.PlatnostOd <= GETDATE() and (cisP2KomunikaceStav.PlatnostDo is null or cisP2KomunikaceStav.PlatnostDo >= GETDATE()) 
      left join P2Formular form on form.Icp = cle.Icp and form.Aktivni = 1
      left join CisCiselnik cisPzs on form.P2StavPzpKod = cisPzs.Kod AND cisPzs.Oblast = N''P2StavPZP'' and cisPzs.PlatnostOd <= GETDATE() and (cisPzs.PlatnostDo is null or cisPzs.PlatnostDo >= GETDATE())  
      left join P2ZarizeniPZS z on form.P2ZarizeniPzsId = z.Id and z.Icz = zar.ICZ and z.PoskytovatelZsId = posk.Id
      join Smlouva smlv on smlv.Id = pril.SmlouvaId
      left join SmluvniVykon smlVyk on smlVyk.CleneniPzsId = cle.Id and smlVyk.PlatnostOd <= GETDATE() and (smlVyk.PlatnostDo is null or smlVyk.PlatnostDo >= GETDATE())
      left join SmlVykonVyjadreniRL vyjadreni on vyjadreni.Id = smlVyk.VyjadreniRLId
    where (@ic is null or (posk.ICO like @ic))
      and (@nz is null or (posk.NazevZkraceny like @nz))
      and (@icz is null or (zar.ICZ like @icz))
      and (@icp is null or (cle.Icp like @icp))
      and (@odb is null or (cle.OdbornostKod = @odb ) or exists(select do.OdbornostKod from DalsiOdbornost do where do.CleneniPzsId = cle.Id and do.OdbornostKod = @odb))
      and (@id is null or (cle.Id = @id))
      and (@cs is null or (smlv.CisloSmlouvy like @cs))
      and (@ts is null or (smlv.SmlouvaTypId = @ts))
      and (@pod is null or ((pril.PriPlatnostDo >= @pod) or (pril.PriPlatnostDo is null)))
      and (@pdo is null or (pril.PriPlatnostOd <= @pdo))
      and (@uod is null or ((pril.UplatneniDo >= @uod) or (pril.UplatneniDo is null)))
      and (@udo is null or (pril.UplatneniOd <= @udo))
      and (@sp is null or (pril.Status = @sp))
      and (@e2 is null or (posk.P2KomunikaceStavKod = @e2))
      and (@tz is null or (zar.TypPzsKod = @tz))
      and (@sv is null or (smlVyk.Status = @sv))
      and (@vyk is null or (smlVyk.VykonKod = @vyk))
      and (@rl is null or (vyjadreni.NavUserId = @rl))
      and (@prl is null or @prl = 0 or exists(select sv.VykonKod from SmluvniVykon sv where sv.Id = smlVyk.Id and sv.Prolongace = 1))
    DELETE FROM @ColumnSort WHERE SortColumn = N''Id''
    select @orderByClause= STUFF((SELECT '', '' + cs.SortColumn + '' '' +  cs.OrderColumn FROM @ColumnSort cs FOR XML PATH('''')), 1, 2, '''')
    if @s is null
      set @Query = ''select * from #Results order by '' + @orderByClause
    else
      set @Query = ''select * from #Results order by '' + @orderByClause + '' OFFSET '' + cast(@p * @s as nvarchar(255)) + '' ROWS FETCH NEXT '' + cast(@s as nvarchar(255)) + '' ROWS ONLY''
    select @totalCount = count(*) from #Results
    execute sp_executesql @Query
    drop table #Results
    END'
    END

我搞错了

不正确的语法接近‘Pril2公式_max_per_id’。SQL State=S0001，DB Errorcode=102

对不起，双倍，这是为文本文件制作的。

如果没有声明"with Pril2Formular_max_per_id“，它就能工作。

对我来说是新的东西，如果有一个常见的错误，很抱歉

Answer 1

你的语法错了。它应该是

; with 
Pril2Formular_max_per_id as
(
    . . . 
)
insert into #Results 
select ...
from   Ordinace ord
       . . .

Answer 2

1. 当CTE在作为批处理的一部分的语句中使用时，前面的语句必须后面跟着分号。
2. 插入命令的正确语法：

[使用，...n ]插入{ TOP (表达式)[百分比]{TOP rowset_function_limited [ WITH ( ...n )]}{( column_list ){值({默认的INSERT }，...n )，...n、derived_table、execute_statement、execute_statement、、默认值}；

就你而言：

    /*statements*/

    INSERT INTO @ColumnSort exec getOrderByClauseTable @Sort=@r, @Order=@o;

    with Pril2Formular_max_per_id as (
        select ord.Id as OrdinaceId, max(pr.Priloha2Id) as max_Priloha2Id
        from Ordinace ord
          left join CleneniPzs cle on cle.Id = ord.CleneniPzsId
          left join Pril2Formular pr on pr.CleneniPzsId = cle.Id
        group by ord.Id
    )
    INSERT into #Results 

    select 
    distinct ord.Id as OrdinaceId,

    /*etc*/