Aleks
asked on
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_D UPS] Script Date: 09/08/2014 16:15:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ADMIN_DEL_CONTACT_D UPS]
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
----
/****** Object: StoredProcedure [dbo].[ADMIN_DEL_CONTACT_D
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ADMIN_DEL_CONTACT_D
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
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
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
FROM Atts
)
DELETE FROM TMP_DUPS WHERE rownum > 1
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked .. thanks !
ASKER