99 lines
3.5 KiB
Transact-SQL
99 lines
3.5 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpOratervTargy
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpOratervTargy
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@oratervTargyId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@kovOratervTargyId int
|
|
,@kovTantargyId int=(
|
|
SELECT tgyKov.ID FROM T_TANTARGY_OSSZES tgyKov
|
|
INNER JOIN T_ORATERVTARGY_OSSZES ottgy ON ottgy.C_TANTARGYID=tgyKov.ELOZOTANEVIREKORDID AND ottgy.TOROLT='F'
|
|
WHERE tgyKov.TOROLT='F' AND tgyKov.C_TANEVID=@kovetkezoTanevId AND ottgy.ID=@oratervTargyId)
|
|
|
|
,@kovOratervId int =(
|
|
SELECT otKov.ID FROM T_ORATERV_OSSZES otKov
|
|
INNER JOIN T_ORATERVTARGY_OSSZES ottgy ON ottgy.ID=@oratervTargyId AND otKov.ELOZOTANEVIREKORDID=ottgy.C_ORATERVID AND ottgy.TOROLT='F'
|
|
WHERE otKov.C_TANEVID=@kovetkezoTanevId AND otKov.TOROLT='F')
|
|
,@sql nvarchar(max)
|
|
,@selectColList nvarchar(max)
|
|
,@insertColList nvarchar(max)
|
|
,@updateColList nvarchar(max)
|
|
|
|
CREATE TABLE #columnMapping(
|
|
selectColName nvarchar(128)
|
|
,insertColName nvarchar(128)
|
|
,updateColName nvarchar(128)
|
|
)
|
|
|
|
IF EXISTS(SELECT 1 FROM T_ORATERVTARGY_OSSZES WHERE TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND C_TANTARGYID=@kovTantargyId AND C_ORATERVID=@kovOratervId AND ISNULL(ELOZOTANEVIREKORDID,0)<>@oratervTargyId)
|
|
BEGIN
|
|
UPDATE T_ORATERVTARGY_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID=@oratervTargyId
|
|
UPDATE T_ORATERVTARGY_OSSZES SET ELOZOTANEVIREKORDID = @oratervTargyId WHERE TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND C_TANTARGYID=@kovTantargyId AND C_ORATERVID=@kovOratervId AND ISNULL(ELOZOTANEVIREKORDID,0)<>@oratervTargyId
|
|
END
|
|
|
|
SELECT @kovOratervTargyId = ID FROM T_ORATERVTARGY_OSSZES WHERE ELOZOTANEVIREKORDID = @oratervTargyId
|
|
|
|
IF (SELECT TOROLT FROM T_ORATERV_OSSZES WHERE ID = @oratervTargyId) = 'T' OR (SELECT TOROLT FROM T_ORATERV_OSSZES WHERE ID = @kovOratervTargyId) = 'T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO #columnMapping(
|
|
selectColName
|
|
,insertColName
|
|
,updateColName
|
|
) SELECT
|
|
selectColName
|
|
,insertColName
|
|
,updateColName
|
|
FROM fnGetFollowUpColumnMapping (NULL,'T_ORATERVTARGY')
|
|
|
|
IF (@kovOratervTargyId 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_ORATERVTARGY_OSSZES (
|
|
'+@insertColList+'
|
|
) SELECT
|
|
'+@selectColList+'
|
|
FROM T_ORATERVTARGY_OSSZES s
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ELOZOTANEVIREKORDID = s.C_TANTARGYID
|
|
INNER JOIN T_ORATERV_OSSZES otKov ON otKov.ELOZOTANEVIREKORDID=s.C_ORATERVID AND otKov.TOROLT=''F''
|
|
WHERE s.ID=@oratervTargyId'
|
|
|
|
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_ORATERVTARGY_OSSZES t
|
|
INNER JOIN T_ORATERVTARGY_OSSZES s ON s.ID = @oratervTargyId AND s.ID=t.ELOZOTANEVIREKORDID
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ELOZOTANEVIREKORDID=s.C_TANTARGYID AND tgy.TOROLT=''F'''
|
|
END
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@oratervTargyId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@oratervTargyId = @oratervTargyId
|
|
|
|
END
|
|
GO |