-- =============================================
-- Description:  Szótár elemeketet updatelő script 
-- =============================================
DROP PROCEDURE IF EXISTS [dev].uspSwitchDictionaryItemBaseFromTmp
GO
CREATE PROCEDURE [dev].uspSwitchDictionaryItemBaseFromTmp
AS
BEGIN
  SET NOCOUNT ON;
IF (EXISTS (SELECT 1 FROM sys.tables WHERE name = 'tmpForSwitchDictionaryItemBase'))
BEGIN
DECLARE @sql nvarchar(max) = N''
SELECT @sql += N'
     UPDATE ' + rt.name + ' SET ' + rc.name + ' = '+ CAST(tmp.UjID AS nvarchar(10)) + N'
     WHERE '+ rc.name +' = ' + CAST(tmp.RegiID as nvarchar(10)) + N'
        AND ' + ctanevId.name + ' = ' + CAST(tmp.TanevID as nvarchar(10))
FROM sys.foreign_keys fk
  INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
  INNER JOIN sys.tables rt ON fkc.parent_object_id = rt.object_id
  INNER JOIN sys.columns rc ON rc.column_id = fkc.parent_column_id AND rc.object_id =   rt.object_id
  INNER JOIN sys.columns c ON c.column_id = fkc.referenced_column_id AND c.object_id = fkc.referenced_object_id
  INNER JOIN sys.tables fkt ON fkt.object_id = fkc.referenced_object_id
  INNER JOIN tmpForSwitchDictionaryItemBase tmp ON fkt.name = 'T_'+ tmp.C_TYPE AND tmp.UjID IS NOT NULL
    INNER JOIN sys.columns ctanevId ON ctanevId.object_id = rt.object_id AND ctanevId.name IN (N'C_ALTANEVID',N'C_TANEVID') 
  WHERE rc.name NOT IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID',N'C_ALTANEVID',N'C_TANEVID', 'ID')
    AND tmp.RegiID <> tmp.UjID

EXEC sp_executesql @sql

DECLARE @torolsql nvarchar(max) = N''
SELECT @torolsql += N'
     UPDATE ' + rt.name + ' SET TOROLT = ''T''
     WHERE '+ rc.name +' = ' + CAST(tmp.RegiID as nvarchar(10)) + N'
        AND ' + ctanevId.name + ' = ' + CAST(tmp.TanevID as nvarchar(10))
FROM sys.foreign_keys fk
  INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
  INNER JOIN sys.tables rt ON fkc.parent_object_id = rt.object_id
  INNER JOIN sys.columns rc ON rc.column_id = fkc.parent_column_id AND rc.object_id =   rt.object_id
  INNER JOIN sys.columns c ON c.column_id = fkc.referenced_column_id AND c.object_id = fkc.referenced_object_id
  INNER JOIN sys.tables fkt ON fkt.object_id = fkc.referenced_object_id
  INNER JOIN tmpForSwitchDictionaryItemBase tmp ON fkt.name = 'T_'+ tmp.C_TYPE AND (tmp.UjID IS NULL OR tmp.Torolni = 'T')
    INNER JOIN sys.columns ctanevId ON ctanevId.object_id = rt.object_id AND ctanevId.name IN (N'C_ALTANEVID',N'C_TANEVID') 
  WHERE rc.name NOT IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID',N'C_ALTANEVID',N'C_TANEVID', 'ID')
    AND tmp.RegiID <> tmp.UjID

EXEC sp_executesql @torolsql

DROP TABLE IF EXISTS tmpForSwitchDictionaryItemBase 
END
END