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

Question

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?

WITH is an expression it has to be followed by a SELECT or DML statement. — Thom A
– Thom A ♦, Commented Sep 30, 2019 at 8:59
A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view — Red Devil
– Red Devil, Commented Sep 30, 2019 at 9:00

Ed Bangga · Accepted Answer · 2019-09-30 09:03:45Z

use @@rowcount instead of exist

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 )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 IF @@rowcount > 0 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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related