TSQL CTE 错误 : Incorrect syntax near ')'

Question

我正在使用 SSMS 2008 开发 TSQL 存储过程，并且在生成 CTE 时收到上述错误。我想给这个 SP 添加逻辑以每天返回，而不仅仅是有数据的日子。我该怎么做呢？到目前为止，这是我的 SP:

ALTER Proc [dbo].[rpt_rd_CensusWithChart]
   @program uniqueidentifier = NULL,
   @office uniqueidentifier = NULL
AS
DECLARE @a_date datetime
SET @a_date = case when MONTH(GETDATE()) >= 7 THEN '7/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30))
ELSE '7/1/' + CAST(YEAR(GETDATE())-1 AS VARCHAR(30)) END

if exists (
    select  * from tempdb.dbo.sysobjects o    where o.xtype in ('U')  and o.id = object_id(N'tempdb..#ENROLLEES')
) DROP TABLE #ENROLLEES;
if exists (
    select  * from tempdb.dbo.sysobjects o    where o.xtype in ('U')  and o.id = object_id(N'tempdb..#DISCHARGES')
) DROP TABLE #DISCHARGES;

declare @sum_enrollment int

set @sum_enrollment =  
(select sum(1)
from enrollment_view A
join enrollment_info_expanded_view C on A.enrollment_id = C.enroll_el_id 
where
   (@office is NULL OR A.group_profile_id = @office)

   AND (@program is NULL OR A.program_info_id = @program)
and (C.pe_end_date IS NULL OR C.pe_end_date > @a_date)
AND C.pe_start_date IS NOT NULL and C.pe_start_date < @a_date)

select 
A.program_info_id as [Program code], 
A.[program_name],
A.profile_name as Facility, 
A.group_profile_id as Facility_code, 
A.people_id, 
1 as enrollment_id,

C.pe_start_date,
C.pe_end_date,
LEFT(datename(month,(C.pe_start_date)),3) as a_month,
day(C.pe_start_date) as a_day,
@sum_enrollment as sum_enrollment

into #ENROLLEES
from enrollment_view A
join enrollment_info_expanded_view C on A.enrollment_id = C.enroll_el_id 
where
   (@office is NULL OR A.group_profile_id = @office)
   AND (@program is NULL OR A.program_info_id = @program)
and (C.pe_end_date IS NULL OR C.pe_end_date > @a_date)

AND C.pe_start_date IS NOT NULL and C.pe_start_date >= @a_date

;WITH #ENROLLEES AS (
    SELECT '7/1/11' AS dt
    UNION ALL
    SELECT DATEADD(d, 1, pe_start_date) as dt
      FROM #ENROLLEES s
     WHERE DATEADD(d, 1, pe_start_date) <= '12/1/11')

Answer 1

最明显的问题(也可能是导致错误消息的问题)是缺少最后一个 CTE 应该属于的实际语句。我认为它应该是一个 SELECT 语句，它将 CTE 的结果集与 #ENROLLEES 表中的数据相结合。

这就是另一个问题出现的地方。

你看，除了以单个 # 开头的名称对于任何不是本地临时表(CTE 实际上不是表)的任何东西来说都是不可取的，你'我们还为您的 CTE 选择了一个已经属于 existing 表(更准确地说，属于已经提到的 #ENROLLEES 临时表)的特定名称，以及您要使用的名称也可以从中提取数据。您绝对不应将现有表的名称用于 CTE，否则由于名称冲突，您将无法将其与 CTE 连接。

根据其代码，最后一个 CTE 似乎还表示您说要添加到 SP 的逻辑未完成的实现。我可以提出一些想法，但在我继续之前，我希望您意识到您的帖子中实际上有两个不同的请求。一个是关于查找错误消息的原因，另一个是关于新逻辑的代码。一般来说，您最好将此类请求分成不同的问题，因此您也可能属于这种情况。

无论如何，这是我的建议:

• 构建您希望在结果集中考虑的日期的完整列表(这就是 CTE 的用途)；

• 将该列表与 #ENROLLEES 表左连接，为现有日期选择数据，为不存在的日期选择一些默认值或 NULL。

可以这样实现:

… /* all your code up until the last WITH */
;
WITH cte AS (
  SELECT CAST('7/1/11' AS date) AS dt
  UNION ALL
  SELECT DATEADD(d, 1, dt) as dt
  FROM cte
  WHERE dt < '12/1/11'
)
SELECT
  cte.dt,
  tmp.[Program code],
  tmp.[program_name],
  … /* other columns as necessary; you might also consider
       enveloping some or all of the "tmp" columns in ISNULLs,
       like in

         ISNULL(tmp.[Program code], '(none)') AS [Program code]

       to provide default values for absent data */
FROM cte
  LEFT JOIN #ENROLLEES tmp ON cte.dt = tmp.pe_start_date
;