lundi 30 septembre 2019

How to fix 'Wrong syntax near the IF statment'?

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