SQL Server WITH clause

Question

I get this error when using WITH clause

Incorrect syntax next to the 'with' keyword. If this statement is a generic table expression, an xmlnamespaces clause, or is tracking context changes, the previous statement must be interrupted by a semicolon.
Msg 102, Level 15, State 1, ViewComplaintbyProfile Procedure, Line 29
Invalid syntax next to ','.

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

Answer 1

The error message already tells you what to do:

.... the previous statement must be completed with a semicolon.

Try placing the WITH statement in your own block by adding it with a semicolon:

 ALTER PROCEDURE [dbo].[ViewComplaintbyProfile] ( @ID int ) AS BEGIN SET NOCOUNT ON ; WITH one AS ...... ......... 

Answer 2

The way forward is to end each SQL statement with a semicolon, for example. (disabling CTE definitions for readability):

 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