121 lines
4.3 KiB
Transact-SQL
121 lines
4.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpMukodesiHely
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpMukodesiHely
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@mukodesiHelyId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@kovetkezoMukodesiHelyId int
|
|
,@ujKovetkezoMukodesiHelyId int
|
|
,@mukodesiHelyNev nvarchar(100)
|
|
,@isKovItemTorolt char(1)
|
|
,@mukodesiHelyKod nvarchar(3)
|
|
,@insertColList nvarchar(max)
|
|
,@selectColList nvarchar(max)
|
|
,@updateColList nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
CREATE TABLE #columnMapping (
|
|
insertColName nvarchar(128) COLLATE DATABASE_DEFAULT
|
|
,selectColName nvarchar(128)
|
|
,updateColName nvarchar(128)
|
|
)
|
|
INSERT INTO #columnMapping (
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
) SELECT
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
FROM fnGetFollowUpColumnMapping (NULL,'T_MUKODESIHELY')
|
|
|
|
SELECT
|
|
@kovetkezoMukodesiHelyId = mh.ID
|
|
,@isKovItemTorolt = mh.TOROLT
|
|
FROM T_MUKODESIHELY_OSSZES mh
|
|
WHERE mh.C_INTEZMENYID = @intezmenyId
|
|
AND mh.C_TANEVID = @kovetkezoTanevId
|
|
AND mh.ELOZOTANEVIREKORDID = @mukodesiHelyId
|
|
|
|
SELECT
|
|
@mukodesiHelyNev = C_NEV
|
|
,@mukodesiHelyKod = C_MUKODESIHELYAZONOSITO
|
|
FROM T_MUKODESIHELY_OSSZES WHERE ID = @mukodesiHelyId AND C_TANEVID=@aktTanevId
|
|
|
|
|
|
SELECT @ujKovetkezoMukodesiHelyId=ID from T_MUKODESIHELY_OSSZES WHERE TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND
|
|
(
|
|
(@mukodesiHelyNev=C_NEV AND ISNULL(@mukodesiHelyKod,0)=ISNULL(C_MUKODESIHELYAZONOSITO,0))
|
|
OR
|
|
(@mukodesiHelyNev=C_NEV AND (@mukodesiHelyKod IS NULL OR NOT EXISTS(SELECT 1 FROM T_MUKODESIHELY_OSSZES WHERE ISNULL(@mukodesiHelyKod,0)=ISNULL(C_MUKODESIHELYAZONOSITO,0) AND ISNULL(ELOZOTANEVIREKORDID,0)<>@mukodesiHelyId AND TOROLT='F' AND C_TANEVID=@kovetkezoTanevId)))
|
|
OR
|
|
(@mukodesiHelyKod=C_MUKODESIHELYAZONOSITO AND NOT EXISTS(SELECT 1 FROM T_MUKODESIHELY_OSSZES WHERE @mukodesiHelyNev=C_NEV AND ISNULL(ELOZOTANEVIREKORDID,0)<>@mukodesiHelyId AND TOROLT='F' AND C_TANEVID=@kovetkezoTanevId))
|
|
)
|
|
AND ISNULL(ELOZOTANEVIREKORDID,0)<>@mukodesiHelyId
|
|
|
|
IF ISNULL(@kovetkezoMukodesiHelyId,0) <> @ujKovetkezoMukodesiHelyId BEGIN
|
|
UPDATE T_MUKODESIHELY_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ID = @kovetkezoMukodesiHelyId
|
|
UPDATE T_MUKODESIHELY_OSSZES SET ELOZOTANEVIREKORDID = @mukodesiHelyId WHERE ID = @ujKovetkezoMukodesiHelyId
|
|
SET @kovetkezoMukodesiHelyId=@ujKovetkezoMukodesiHelyId
|
|
END
|
|
|
|
IF (SELECT TOROLT FROM T_MUKODESIHELY_OSSZES WHERE ID = @mukodesiHelyId)='T'
|
|
OR (SELECT TOROLT FROM T_MUKODESIHELY_OSSZES WHERE ELOZOTANEVIREKORDID = @mukodesiHelyId)='T'
|
|
OR EXISTS (
|
|
SELECT 1 FROM T_MUKODESIHELY_OSSZES
|
|
WHERE ((C_NEV=@mukodesiHelyNev AND (ISNULL(C_MUKODESIHELYAZONOSITO,0)<>@mukodesiHelyKod OR @mukodesiHelyKod IS NULL))
|
|
OR (C_NEV<>@mukodesiHelyNev AND ISNULL(C_MUKODESIHELYAZONOSITO,0) = @mukodesiHelyKod))
|
|
AND TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND ISNULL(ELOZOTANEVIREKORDID,0)<>@mukodesiHelyId)
|
|
BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF (@kovetkezoMukodesiHelyId 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_MUKODESIHELY_OSSZES (
|
|
'+@insertColList+'
|
|
)
|
|
SELECT
|
|
'+@selectColList+'
|
|
FROM T_MUKODESIHELY_OSSZES s
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.ELOZOTANEVIREKORDID=s.C_INTEZMENYADATOKID AND ia.TOROLT=''F''
|
|
WHERE s.ID=@mukodesiHelyId'
|
|
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_MUKODESIHELY_OSSZES t
|
|
INNER JOIN T_MUKODESIHELY_OSSZES s ON s.ID = @mukodesiHelyId and s.TOROLT=''F'' AND s.ID=t.ELOZOTANEVIREKORDID'
|
|
END
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@mukodesiHelyId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@mukodesiHelyId = @mukodesiHelyId
|
|
|
|
|
|
END
|
|
GO |