在存储过程中使用CTE进行问题

I have a stored procedure like this:

我有一个这样的存储过程：

CREATE PROCEDURE prtl_usp_portal_logdaily 
    @startdate DATETIME,
    @enddate DATETIME,
    @usertype int,
    @gradelist varchar(1000) = null
AS
BEGIN
    DECLARE @qry NVARCHAR(MAX)
    SET @qry=''

    IF(@usertype = 1)
    BEGIN
       ;WITH mycte AS
       (
           SELECT CAST(@startdate AS DATETIME) DateValue
           UNION ALL
           SELECT DateValue + 1
           FROM mycte   
           WHERE DateValue + 1 <= @enddate
      )

      SET @qry = N'SELECT datepart(day,DateValue) AS daycode,
   (select count(DISTINCT a.userID) from PRTL_UserAccessLog a
   inner join prtlv_familydetails b
   on a.userid=b.familyid 
   INNER JOIN STD_StudentDetails c ON b.FamilyID = c.familyid
    INNER JOIN dbo.STD_CurrentAcademicInformation d ON c.StudentID = d.StudentID
   WHERE d.GradeID IN ('+@gradelist+')
   and a.usertypeid=1 and convert(date,DateTime) = DateValue) countvalue,
   (select count(*) from prtlv_familydetails) AS total_count,
   ROUND((CAST((select count(DISTINCT a.userID) from PRTL_UserAccessLog a
   inner join prtlv_familydetails b
   on a.userid=b.familyid 
   INNER JOIN STD_StudentDetails c ON b.FamilyID = c.familyid
    INNER JOIN dbo.STD_CurrentAcademicInformation d ON c.StudentID = d.StudentID
   WHERE d.GradeID IN ('+@gradelist+')
   and a.usertypeid=1 and convert(date,DateTime) = DateValue) AS FLOAT)/CAST((select count(*) from prtlv_familydetails)AS FLOAT)),3) AS average, 
   datename(dw,DateValue) AS day_name 
   FROM    mycte';
END
---
 EXEC (@qry)
 END

走

When I run the procedure, I get the following error:

当我运行该过程时，我收到以下错误：

Incorrect syntax near the keyword 'SET'.

关键字“SET”附近的语法不正确。

After googling, I have found that if with cte is not the first statement, semicolon should be placed before cte statement. I have put the semicolon. But it is not working.

谷歌搜索后，我发现如果cte不是第一个语句，分号应该放在cte语句之前。我把分号。但它没有用。

1 个解决方案

#1

WITH needs to be followed by SELECT (or UPDATE or DELETE or INSERT) but not SET, and at least one of the common table expressions defined should be referenced by the following statement. You do not follow WITH by SELECT (or UPDATE or DELETE or INSERT).

WITH需要遵循SELECT（或UPDATE或DELETE或INSERT）而不是SET，并且至少有一个定义的公用表表达式应该由以下语句引用。您不通过SELECT（或UPDATE或DELETE或INSERT）跟随WITH。

Additionally the scope of the dynamic sql will not recognize a common table expression (cte) formed by the procedure prior to execution of the query string.

此外，动态sql的范围将无法识别在执行查询字符串之前由过程形成的公用表表达式（cte）。

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

指定临时命名结果集，称为公用表表达式（CTE）。这是从简单查询派生而来，并在单个SELECT，INSERT，UPDATE或DELETE语句的执行范围内定义。此子句也可以在CREATE VIEW语句中用作其定义SELECT语句的一部分。

see: WITH common_table_expression

请参阅：WITH common_table_expression

#1