When I'm trying to create such stored procedure with CTE:
CREATE PROCEDURE bng_normalize_ps
@ps_id uniqueidentifier,
@u_name VARCHAR(50),
@u_type VARCHAR(50)
AS
BEGIN
WITH My_Recursive (link, n_type, n_name, F_Parent )
AS
(
SELECT
i.link
, t.C_Name n_type
, i.C_Name as n_name
, s.F_Parent
FROM ED_Network_Items i
JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
JOIN ED_Network_Struct s on s.F_Child = i.LINK
WHERE
i.link = @ps_id
UNION ALL
SELECT
i.link
, t.C_Name n_type
, i.C_Name as n_name
, s.F_Parent
FROM ED_Network_Items i
JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
JOIN ED_Network_Struct s on s.F_Child = i.LINK
JOIN My_Recursive r ON s.F_Parent = r.link
)
IF EXISTS(SELECT n_name FROM My_Recursive r WHERE n_name LIKE '%' + @u_name + '%' AND n_type = @u_type GROUP BY n_name HAVING COUNT(*) > 1)
BEGIN
SELECT CONCAT('DELETE DOUBLES for ', @u_type, ' ', @u_name)
RETURN
END
SELECT link, n_type, n_name, F_Parent
FROM My_Recursive r
WHERE
n_name LIKE '%' + @u_name + '%'
AND n_type = @u_type
END
SQL Server returns:
Wrong syntax near the If statement.
I was investigating the manual and didn't find what's up. What's wrong with this code?
Aucun commentaire:
Enregistrer un commentaire