T-SQL将CTE选择为变量
我正在尝试为SQL Server / T-SQL中的CTE选择语句的结果设置一个变量：
DECLARE @ReportRecipients VARCHAR(MAX);
SET @ReportRecipients = WITH CTE_TableName AS (SELECT a.emailTExt FROM
CSLEventAUP_Edit a JOIN CSLEventAUP_EventEditJunction b ON (a.Id = b.EditId)
JOIN CSLEventAUP_Events c ON (b.EventId = c.Id)
WHERE c.LogicalDeleteIn = 0
AND c.StaffEvent = 1
AND a.emailText IS NOT NULL
UNION
SELECT emailText FROM CSLEventAUP_Edit WHERE CyberGroup = 1)
SELECT TOP 1 STUFF((
SELECT ';' + emailTExt
FROM CTE_TableName t1
ORDER BY t1.emailText
FOR XML PATH('')),1, Len(';'), '') AS EmailTexts
FROM CTE_Tablename t0;
我得到以下错误：
msg 156，第15级，状态1，过程emailsignaupreminder，第38行 [批次开始行9]
关键字附近的语法不正确。msg 319，15级，状态1，过程emailsignaupreminder，第38行[批次开始行9]
关键字“ with with”的语法不正确。如果此语句是一个常见的表表达式，则XMLNamespaces子句或更改跟踪上下文子句，则必须用半词来终止先前的语句。
单独运行查询会导致
[email protected];[email protected];[email protected];[email protected]
I am attempting to set a variable to the result of a CTE select statement in SQL Server / T-SQL:
DECLARE @ReportRecipients VARCHAR(MAX);
SET @ReportRecipients = WITH CTE_TableName AS (SELECT a.emailTExt FROM
CSLEventAUP_Edit a JOIN CSLEventAUP_EventEditJunction b ON (a.Id = b.EditId)
JOIN CSLEventAUP_Events c ON (b.EventId = c.Id)
WHERE c.LogicalDeleteIn = 0
AND c.StaffEvent = 1
AND a.emailText IS NOT NULL
UNION
SELECT emailText FROM CSLEventAUP_Edit WHERE CyberGroup = 1)
SELECT TOP 1 STUFF((
SELECT ';' + emailTExt
FROM CTE_TableName t1
ORDER BY t1.emailText
FOR XML PATH('')),1, Len(';'), '') AS EmailTexts
FROM CTE_Tablename t0;
I get these errors:
Msg 156, Level 15, State 1, Procedure EmailSignAUPReminder, Line 38
[Batch Start Line 9]
Incorrect syntax near the keyword 'With'.Msg 319, Level 15, State 1, Procedure EmailSignAUPReminder, Line 38 [Batch Start Line 9]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Running the query by itself results in
[email protected];[email protected];[email protected];[email protected]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要定义您的CTE并在两个单独的步骤中选择其结果。由于这是结构化的，您正在尝试从尚不存在的表达式中进行选择。
You need to define your CTE and SELECT its results in two separate steps. As this is structured, you are attempting to SELECT from an expression that does not yet exist.