SQL Server WITH 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6771410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server WITH clause
提问by Sun
I am getting this error when using a WITH clause
使用 WITH 子句时出现此错误
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.
Msg 102, Level 15, State 1, Procedure ViewComplaintbyProfile, Line 29
Incorrect syntax near ','.
关键字“with”附近的语法不正确。如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号终止。
消息 102，级别 15，状态 1，过程 ViewComplaintbyProfile，第 29 行
“,”附近的语法不正确。
Here is my Procedure
这是我的程序
ALTER PROCEDURE [dbo].[ViewComplaintbyProfile]
( @ID int
)
AS
BEGIN
SET NOCOUNT ON
WITH
one as
(Select sno = ROW_NUMBER()OVER (order by complaint_id), Complaint_Id, ComplainantName,ComplaintType_id, complaintProfileId,ComplainantProfileId,Description,
Email, Date_Complained, Status, AdminComments, Phone, Evidence,
PLevel = CASE PriorityLevel_id WHEN '1' THEN 'High'
WHEN '2' THEN 'Medium'
WHEN '3' THEN 'Low' END ,
Complaint_Type = CASE ComplaintType_ID WHEN '1' THEN 'Purchased Contact has incorrect details'
WHEN '2' THEN 'Contacted Profile is already married'
WHEN '3' THEN 'Suspect the Profile has fradudelent contect/credentials'
WHEN '4' THEN 'Suspect the Profile has fake picture'
WHEN '5' THEN 'Profile has obscene or inappropriate content'
WHEN '6' THEN 'Report harassment, offensive remarks, etc., by user'
WHEN '7' THEN 'Miscellaneous issue' END,
Status1 = CASE Status WHEN 'New' THEN 1
WHEN 'In-Progress' THEN 2
WHEN 'Closed' THEN 3
END
from Complaints),
two as
(SELECT sno = ROW_NUMBER()OVER (order by complaint_id), Complaint.complaintProfileId,
CASE
WHEN cast(mmbProfiles.MMB_Id as varchar) IS NOT NULL THEN cast(mmbProfiles.MMB_Id as varchar)
WHEN cast(UPPMembership.profile_id as varchar) IS NOT NULL THEN 'UPP'
ELSE 'Not found'
END as MMBId
FROM Complaints Complaint
LEFT JOIN MMBMembership
ON MMBMembership.profile_id = Complaint.complaintProfileId
left JOIN MMB_BusinessProfiles mmbProfiles
ON mmbProfiles.MMB_id = MMBMembership.MMB_id
LEFT JOIN UPPMembership
ON UPPMembership.profile_id = Complaint.complaintProfileId)
SELECT one.*,two.MMBId FROM one join two
on one.sno = two.sno
WHERE (ComplaintType_id = @ID)
END
Please help
请帮忙
Thanks Sun
谢谢孙
采纳答案by marc_s
The error message already tells you what to do:
错误消息已经告诉你该怎么做：
.... the previous statement must be terminated with a semicolon.
.... 前面的语句必须以分号结束。
Try putting the WITHstatement into a block of its own by prepending it by a semicolon:
尝试WITH通过在它前面加上分号将语句放入自己的块中：
ALTER PROCEDURE [dbo].[ViewComplaintbyProfile]
( @ID int
)
AS
BEGIN
SET NOCOUNT ON
; WITH one AS ......
.........
回答by onedaywhen
The way forwards is to terminate everySQL statement with a semicolon e.g. (snipping the CTE definitions to aid readability):
前进的方法是用分号终止每个SQL 语句，例如（剪下 CTE 定义以提高可读性）：
ALTER PROCEDURE [dbo].[ViewComplaintbyProfile]
( @ID INT
)
AS
BEGIN; <-- HERE
SET NOCOUNT ON; -- <-- HERE
WITH one AS
(...),
two AS
(...)
SELECT one.*, two.MMBId
FROM one JOIN two
ON one.sno = two.sno
WHERE (ComplaintType_id = @ID); -- <-- HERE
END; -- <-- HERE
