102 lines
No EOL
4.2 KiB
Transact-SQL
102 lines
No EOL
4.2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpTantargy
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpTantargy
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@tantargyId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF NOT EXISTS (SELECT 1 from T_TANEV where ID = @kovetkezoTanevId and C_KOVETKEZO = 'T' and C_AKTIVALVA is null and C_ISKIVALASZTHATO = 'T')
|
|
BEGIN
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE
|
|
@kovetkezoTantargyId int
|
|
,@tantargyNev nvarchar(510)=(SELECT C_NEV FROM T_TANTARGY_OSSZES WHERE ID = @tantargyId)
|
|
,@insertColList nvarchar(max)
|
|
,@selectColList nvarchar(max)
|
|
,@updateColList nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
CREATE TABLE #columnMapping (
|
|
insertColName nvarchar(max) COLLATE DATABASE_DEFAULT
|
|
,selectColName nvarchar(max)
|
|
,updateColName nvarchar(max)
|
|
)
|
|
INSERT INTO #columnMapping (
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
) SELECT
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
FROM fnGetFollowUpColumnMapping (NULL,'T_TANTARGY')
|
|
|
|
UPDATE #columnMapping SET selectColName = 'sz.ID', updateColName = 'sz.ID' where insertColName = 'C_SZERVEZETID'
|
|
|
|
IF EXISTS(SELECT 1 FROM T_TANTARGY_OSSZES WHERE C_TANEVID=@kovetkezoTanevId AND TOROLT='F' AND C_NEV=@tantargyNev AND ISNULL(ELOZOTANEVIREKORDID,0)<>@tantargyId) BEGIN
|
|
UPDATE T_TANTARGY_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID = @tantargyId
|
|
UPDATE T_TANTARGY_OSSZES SET ELOZOTANEVIREKORDID = @tantargyId WHERE C_NEV = @tantargyNev AND TOROLT='F' AND C_TANEVID = @kovetkezoTanevId
|
|
END
|
|
|
|
SELECT @kovetkezoTantargyId = ID FROM T_TANTARGY_OSSZES WHERE ELOZOTANEVIREKORDID = @tantargyId AND C_TANEVID=@kovetkezoTanevId
|
|
|
|
IF (SELECT TOROLT FROM T_TANTARGY_OSSZES WHERE ID = @tantargyId) = 'T' OR (SELECT TOROLT FROM T_TANTARGY_OSSZES WHERE ID = @kovetkezoTantargyId) = 'T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF (@kovetkezoTantargyId IS NULL) BEGIN
|
|
SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMapping
|
|
SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMapping
|
|
SET @sql = N'
|
|
INSERT INTO T_TANTARGY_OSSZES (
|
|
' + @insertColList + '
|
|
) SELECT
|
|
' + @selectColList + '
|
|
FROM T_TANTARGY_OSSZES s
|
|
LEFT JOIN T_TANTARGY_OSSZES ftgy ON ftgy.ELOZOTANEVIREKORDID = s.C_FOTARGYID AND ftgy.TOROLT = ''F'' AND ftgy.C_TANEVID = @kovetkezoTanevId
|
|
INNER JOIN T_TARGYKATEGORIATIPUS_OSSZES tkt ON tkt.ELOZOTANEVIREKORDID = s.C_TARGYKATEGORIA AND tkt.TOROLT = ''F'' AND tkt.C_ALTANEVID = @kovetkezoTanevId
|
|
LEFT JOIN T_TANSZAKTIPUS_OSSZES tszt ON tszt.ELOZOTANEVIREKORDID = s.C_TANSZAKTIPUSID AND tszt.TOROLT = ''F'' AND tszt.C_ALTANEVID = @kovetkezoTanevId
|
|
LEFT JOIN T_SZERVEZET_OSSZES sz ON sz.ELOZOTANEVIREKORDID = s.C_SZERVEZETID AND sz.TOROLT = ''F''
|
|
WHERE s.ID = @tantargyId'
|
|
|
|
END
|
|
ELSE BEGIN
|
|
SELECT
|
|
@updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
|
|
FROM #columnMapping
|
|
WHERE updateColname IS NOT NULL
|
|
SET @sql = N'
|
|
UPDATE t SET
|
|
' + @updateColList + '
|
|
FROM T_TANTARGY_OSSZES t
|
|
INNER JOIN T_TANTARGY_OSSZES s ON s.ID = @tantargyId AND s.ID = t.ELOZOTANEVIREKORDID
|
|
LEFT JOIN T_TANTARGY_OSSZES ftgy ON ftgy.ELOZOTANEVIREKORDID = s.C_FOTARGYID AND ftgy.TOROLT = ''F'' AND ftgy.C_TANEVID = @kovetkezoTanevId
|
|
INNER JOIN T_TARGYKATEGORIATIPUS_OSSZES tkt ON tkt.ELOZOTANEVIREKORDID=s.C_TARGYKATEGORIA AND tkt.TOROLT = ''F'' AND tkt.C_ALTANEVID = @kovetkezoTanevId
|
|
LEFT JOIN T_TANSZAKTIPUS_OSSZES tszt ON tszt.ELOZOTANEVIREKORDID = s.C_TANSZAKTIPUSID AND tszt.TOROLT = ''F'' AND tszt.C_ALTANEVID = @kovetkezoTanevId
|
|
LEFT JOIN T_SZERVEZET_OSSZES sz ON sz.ELOZOTANEVIREKORDID = s.C_SZERVEZETID AND sz.TOROLT = ''F''
|
|
'
|
|
END
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@tantargyId int
|
|
,@kovetkezoTantargyId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@tantargyId = @tantargyId
|
|
,@kovetkezoTantargyId = @kovetkezoTantargyId
|
|
|
|
END
|
|
GO |