Print within the SQL Server Select query based on the condition-CodePudding

I have this query from the link here which works fine, only that I am adding include FK constraints 'ON Delete/ Update cascade' if delete/update_referential_action is 1. I am unable to achieve the desired result. Any help appreciated.

SELECT @create  = N'
ALTER TABLE ' 
     QUOTENAME(cs.name)   '.'   QUOTENAME(ct.name) 
     ' ADD CONSTRAINT '   QUOTENAME(fk.name) 
     ' FOREIGN KEY ('   STUFF((SELECT ','   QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
    ') REFERENCES '   QUOTENAME(rs.name)   '.'   QUOTENAME(rt.name)
    '('   STUFF((SELECT ','   QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 

**--- Trying to Accomplish --** 

If delete_referential_action = 1 THEN
    
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')   ') ON DELETE CASCADE ;'
    
    ELSE
    
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')   ');'

**----- xxxxx ------**

FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

CodePudding user response：

Ms Sql 2016 dose not support if statement in middle of query. You must use case statement instead of it.

you need to change

**--- Trying to Accomplish --** 

If delete_referential_action = 1 THEN
    
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')   ') ON DELETE CASCADE ;'
    
    ELSE
    
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')   ');'

**----- xxxxx ------**

to something like this

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')   ')'  
CASE delete_referential_action
WHEN 1 THEN
  N'ON DELETE CASCADE'
END
  ';'