121 lines
4.6 KiB
Transact-SQL
121 lines
4.6 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpAlkalmazottVegzettseg
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpAlkalmazottVegzettseg
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@vegzettsegId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@kovVegzettsegId int
|
|
,@selectColListV nvarchar(max)
|
|
,@insertColListV nvarchar(max)
|
|
,@updateColListV nvarchar(max)
|
|
,@selectColListKV nvarchar(max)
|
|
,@insertColListKV nvarchar(max)
|
|
,@updateColListKV nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
SET @kovVegzettsegId = (SELECT ID FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID=@vegzettsegId)
|
|
IF (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ID = @vegzettsegId)='T' OR (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID = @vegzettsegId)='T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
CREATE TABLE #ColumnMapping (selectColName nvarchar(128),insertColName nvarchar(128),updateColName nvarchar(128),tableName nvarchar(128))
|
|
INSERT INTO #ColumnMapping(
|
|
selectColName
|
|
,insertColname
|
|
,updateColname
|
|
,tableName
|
|
)
|
|
SELECT
|
|
selectColName
|
|
,insertColName
|
|
,updateColName
|
|
,'T_VEGZETTSEG'
|
|
FROM fnGetFollowUpColumnMapping (NULL,'T_VEGZETTSEG')
|
|
UNION ALL
|
|
SELECT
|
|
selectColName
|
|
,insertColName
|
|
,updateColName
|
|
,'T_KEPESITES_VEGZETTSEG'
|
|
FROM fnGetFollowUpColumnMapping ('ELOZOTANEVIREKORDID','T_KEPESITES_VEGZETTSEG')
|
|
|
|
IF @kovVegzettsegId IS NULL BEGIN
|
|
SELECT @insertColListV = ISNULL(@insertColListV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_VEGZETTSEG'
|
|
SELECT @selectColListV = ISNULL(@selectColListV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_VEGZETTSEG'
|
|
SELECT @insertColListKV = ISNULL(@insertColListKV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
|
|
SELECT @selectColListKV = ISNULL(@selectColListKV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
|
|
|
|
SET @sql = N'
|
|
INSERT INTO T_VEGZETTSEG_OSSZES(
|
|
'+@insertColListV+'
|
|
) SELECT
|
|
'+@selectColListV+'
|
|
FROM T_VEGZETTSEG_OSSZES s
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ELOZOTANEVIREKORDID=s.C_TANARID AND fh.TOROLT=''F''
|
|
WHERE s.ID=@vegzettsegId
|
|
|
|
INSERT INTO T_KEPESITES_VEGZETTSEG (
|
|
'+@insertColListKV+'
|
|
) SELECT
|
|
'+@selectColListKV+'
|
|
FROM T_VEGZETTSEG_OSSZES s
|
|
INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=s.ID
|
|
INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT=''F''
|
|
INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=s.ID
|
|
WHERE s.ID = @vegzettsegId'
|
|
END
|
|
ELSE BEGIN
|
|
SELECT
|
|
@updateColListV = ISNULL(@updateColListV+','+insertColname+' = '+updateColname,insertColName+' = '+updateColname)
|
|
FROM #columnMapping WHERE updateColName IS NOT NULL AND tableName = 'T_VEGZETTSEG'
|
|
SELECT @insertColListKV = ISNULL(@insertColListKV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
|
|
SELECT @selectColListKV = ISNULL(@selectColListKV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
|
|
|
|
DELETE kv
|
|
FROM T_KEPESITES_VEGZETTSEG kv
|
|
INNER JOIN T_KEPESITES_OSSZES kKov ON kKov.ID=kv.C_KEPESITESID AND kKov.TOROLT='F'
|
|
INNER JOIN T_KEPESITES_OSSZES kOld ON kOld.ID=kKov.ELOZOTANEVIREKORDID AND kOld.TOROLT='F'
|
|
LEFT JOIN T_KEPESITES_VEGZETTSEG kvOld ON kvOld.C_VEGZETTSEGID=@vegzettsegId AND kvOld.C_KEPESITESID=kOld.ID
|
|
WHERE kv.C_VEGZETTSEGID=@kovVegzettsegId AND kvOld.C_KEPESITESID IS NULL
|
|
|
|
SET @sql =N'
|
|
UPDATE t SET
|
|
'+@updateColListV+'
|
|
FROM T_VEGZETTSEG_OSSZES s
|
|
INNER JOIN T_VEGZETTSEG_OSSZES t ON t.ELOZOTANEVIREKORDID = s.ID AND s.ID=@vegzettsegId
|
|
|
|
INSERT INTO T_KEPESITES_VEGZETTSEG (
|
|
'+@insertColListKV+'
|
|
) SELECT
|
|
'+@selectColListKV+'
|
|
FROM T_VEGZETTSEG_OSSZES s
|
|
INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=s.ID
|
|
INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT=''F''
|
|
INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=s.ID
|
|
WHERE s.ID=@vegzettsegId AND NOT EXISTS (SELECT 1 FROM T_KEPESITES_VEGZETTSEG WHERE C_VEGZETTSEGID=@kovVegzettsegId AND C_KEPESITESID=k.ID)'
|
|
END
|
|
|
|
--PRINT @sql
|
|
exec sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@vegzettsegId int
|
|
,@kovVegzettsegId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@vegzettsegId = @vegzettsegId
|
|
,@kovVegzettsegId = @kovVegzettsegId
|
|
|
|
|
|
END
|
|
GO
|