SQL Server WITH clause

Question

I am getting this error when using a WITH clause

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 ','.

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

Please get in the habit of properly terminating all applicable statements in T-SQL. More and more keywords are requiring that the previous statement be terminated with a semi-colon. I've been preaching this as a best practice for a few years now, hopefully it will start to catch on. https://sqlblog.org/blogs/aaron_bertrand/archive/2011/07/12/t-sql-tuesday-20-t-sql-best-practices.aspx — Aaron Bertrand, Jul 21 '11 at 05:02
Also, please specify and/or tag the version of SQL Server you are using. While it's clear from the error message that you are using 2005 or greater, it is always useful to have that information more obvious. — Aaron Bertrand, Jul 21 '11 at 05:04
Exact duplicate of [Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon.](http://stackoverflow.com/questions/1439123/incorrect-syntax-near-the-keyword-with-previous-statement-must-be-terminated) and several others — gbn, Jul 21 '11 at 06:03

score 6 · Accepted Answer · answered Jul 21 '11 at 04:59

6

The error message already tells you what to do:

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

Try putting the WITH statement into a block of its own by prepending it by a semicolon:

ALTER PROCEDURE [dbo].[ViewComplaintbyProfile]

(   @ID int


)

AS
BEGIN
    SET NOCOUNT ON

; WITH one AS ......
  .........

answered Jul 21 '11 at 04:59

marc_s

732,580
175
1,330
1,459

...or actually terminating the previous statement, like the error message says. :-) +1 anyway, just giving you a hard time. – Aaron Bertrand Jul 21 '11 at 05:03
@Aaron Bertrand: sheeesh!! :-) Nitpicking galore... :-) – marc_s Jul 21 '11 at 05:04
1

Nitpicking is my middle name... I tried to make it more obvious by including it in my username, but it exceeded the allotted length. :-) – Aaron Bertrand Jul 21 '11 at 05:06

score 0 · Answer 2 · answered Jul 21 '11 at 10:39

The way forwards is to terminate every SQL statement with a semicolon e.g. (snipping the CTE definitions to aid 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

SQL Server WITH clause

2 Answers2