[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

SP Error when creating

I am trying to create a new SP that currently have in one database but when I do I get an error. I used the MS SQL option to Script the SP on a new window. This is the code:

----

/****** Object:  StoredProcedure [dbo].[ADMIN_DEL_CONTACT_DUPS]    Script Date: 09/08/2014 16:15:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[ADMIN_DEL_CONTACT_DUPS]
AS
BEGIN

SELECT
   DISTINCT AttyId,userid
FROM
   dbo.Attscont
GROUP BY
   AttyId,userid
HAVING
   COUNT(1) > 1

WITH TMP_DUPS AS(
   SELECT AttyId, userid, rownum = ROW_NUMBER()OVER(PARTITION BY AttyId, userid ORDER BY AttyId, userid)
   FROM dbo.Attscont
)
DELETE FROM TMP_DUPS WHERE rownum > 1

END


GO

----

This is the error I get

Msg 319, Level 15, State 1, Procedure ADMIN_DEL_CONTACT_DUPS, Line 14
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.

-----

I get similar error for this procedure:

---
/****** Object:  StoredProcedure [dbo].[ADMIN_DEL_CASE_DUPS]    Script Date: 09/08/2014 16:15:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[ADMIN_DEL_CASE_DUPS]
AS
BEGIN

SELECT
   DISTINCT caseid,userid
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1

WITH TMP_DUPS AS(
   SELECT caseid, userid, rownum = ROW_NUMBER()OVER(PARTITION BY caseid, userid ORDER BY caseid, userid)
   FROM Atts
)
DELETE FROM TMP_DUPS WHERE rownum > 1

END

GO
0
Aleks
Asked:
Aleks
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
You need a semi-colon before WITH if another statement precedes WITH:

;WITH TMP_DUPS AS(

Well, technically the ; is after the previous statement, but you can just code it before the WITH :-).
0
 
AleksAuthor Commented:
Let me try ...
0
 
AleksAuthor Commented:
Worked .. thanks !
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now