83 lines
2.4 KiB
Transact-SQL
83 lines
2.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpGondviseloCim
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpGondviseloCim
|
|
@intezmenyId int
|
|
,@tanevId int
|
|
,@kovetkezoTanevId int
|
|
,@cimId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@kovCimId int = (SELECT TOP 1 ID FROM T_CIM_OSSZES WHERE ELOZOTANEVIREKORDID = @cimId AND TOROLT = 'F')
|
|
,@nextGvId int
|
|
,@gvid int
|
|
,@selectColList nvarchar(max)
|
|
,@insertColList nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
CREATE TABLE #GvCimColumnMapping (
|
|
selectColName nvarchar(128)
|
|
,insertColName nvarchar(128)
|
|
)
|
|
INSERT INTO #GvCimColumnMapping (
|
|
selectColName
|
|
,insertColName
|
|
)
|
|
SELECT
|
|
selectColName
|
|
,insertColName
|
|
FROM fnGetFollowUpColumnMapping ('','T_CIM')
|
|
|
|
|
|
|
|
IF EXISTS(SELECT * FROM T_CIM_OSSZES WHERE ID = @cimId AND TOROLT = 'T')
|
|
OR
|
|
EXISTS(SELECT * FROM T_CIM_OSSZES WHERE ID = @kovCimId AND TOROLT = 'T')
|
|
OR
|
|
EXISTS(SELECT * FROM T_CIM_OSSZES WHERE ELOZOTANEVIREKORDID = @cimId AND TOROLT = 'F')
|
|
RETURN
|
|
|
|
|
|
|
|
SELECT
|
|
@gvId = g.ID
|
|
,@nextGvId = gNext.ID
|
|
FROM T_CIM_OSSZES c
|
|
INNER JOIN T_GONDVISELO_OSSZES g ON g.ID=c.C_GONDVISELOID
|
|
INNER JOIN T_GONDVISELO_OSSZES gNext ON gNext.ELOZOTANEVIREKORDID = g.ID AND gNext.TOROLT='F'
|
|
WHERE c.ID = @cimId
|
|
|
|
DELETE FROM T_CIM WHERE C_GONDVISELOID = @nextGvId
|
|
|
|
UPDATE #GvCimColumnMapping SET selectColName = 'fhNew.ID' WHERE insertColName = 'C_FELHASZNALOID'
|
|
UPDATE #GvCimColumnMapping SET selectColName = 'g.ID' WHERE insertColName = 'C_GONDVISELOID'
|
|
SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #GvCimColumnMapping
|
|
SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #GvCimColumnMapping
|
|
SET @sql =N'
|
|
INSERT INTO T_CIM_OSSZES (
|
|
'+@insertColList+'
|
|
) SELECT
|
|
'+@selectColList+'
|
|
FROM T_CIM_OSSZES s
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = s.C_FELHASZNALOID
|
|
INNER JOIN T_FELHASZNALO_OSSZES fhNew ON fhNew.C_OKTATASIAZONOSITO=fh.C_OKTATASIAZONOSITO AND fhNew.TOROLT=''F'' AND fhNew.C_TANEVID=@kovetkezoTanevId
|
|
INNER JOIN T_GONDVISELO_OSSZES g ON g.ELOZOTANEVIREKORDID=s.C_GONDVISELOID AND g.TOROLT=''F''
|
|
WHERE s.C_GONDVISELOID = @gvId'
|
|
|
|
--print @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@tanevId int
|
|
,@kovetkezoTanevId int
|
|
,@gvId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@tanevId = @tanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@gvId = @gvId
|
|
|
|
|
|
END
|
|
GO |