76 lines
3.5 KiB
Transact-SQL
76 lines
3.5 KiB
Transact-SQL
IF OBJECT_ID('sp_FollowUpDictionaryItemBase') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_FollowUpDictionaryItemBase
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_FollowUpDictionaryItemBase
|
|
@pItemId int
|
|
,@pCurrentTanevId int
|
|
,@pNextTanevId int
|
|
,@pIntezmenyId int
|
|
AS
|
|
BEGIN
|
|
DECLARE @typeName nvarchar(64)
|
|
,@typeSQL nvarchar(max)
|
|
|
|
SELECT TOP 1 @typeName=C_TYPE
|
|
FROM T_DICTIONARYITEMBASE db
|
|
WHERE db.ID=@pItemId AND db.C_INTEZMENYID=@pIntezmenyId AND db.C_TANEVID=@pCurrentTanevId
|
|
|
|
--Item Base Merge START--
|
|
SET IDENTITY_INSERT T_DICTIONARYITEMBASE ON
|
|
MERGE INTO T_DICTIONARYITEMBASE AS trg
|
|
USING (SELECT TOP 1 *
|
|
FROM T_DICTIONARYITEMBASE db
|
|
WHERE db.ID=@pItemId AND db.C_INTEZMENYID=@pIntezmenyId AND db.C_TANEVID=@pCurrentTanevId)
|
|
AS src
|
|
ON trg.ID=src.ID AND trg.C_TANEVID= @pNextTanevId AND src.C_TANEVID=@pCurrentTanevId AND trg.C_INTEZMENYID=src.C_INTEZMENYID
|
|
WHEN MATCHED THEN
|
|
UPDATE SET C_VALUE=src.c_VALUE
|
|
,C_NAME=src.C_NAME
|
|
,C_NAME_1=src.C_NAME_1
|
|
,C_NAME_2=src.C_NAME_2
|
|
,C_NAME_3=src.C_NAME_3
|
|
,C_NAME_4=src.C_NAME_4
|
|
,C_VISIBLE=src.C_VISIBLE
|
|
,C_CODE=src.C_CODE
|
|
,C_TYPE=src.C_TYPE
|
|
,C_PROTECTED=src.C_PROTECTED
|
|
,C_COLOR=src.C_COLOR
|
|
,C_ORDER=src.C_ORDER
|
|
,C_BGCOLOR=src.C_BGCOLOR
|
|
,C_DESCRIPTION=src.C_DESCRIPTION
|
|
,C_ISACTIVE=src.C_ISACTIVE
|
|
,C_SHORTNAME=src.C_SHORTNAME
|
|
,C_DICTIONARYTYPEID=src.C_DICTIONARYTYPEID
|
|
,C_INTEZMENYID=src.C_INTEZMENYID
|
|
,C_TANEVID=@pNextTanevId
|
|
,TOROLT=src.TOROLT
|
|
,SERIAL=src.SERIAL
|
|
,LASTCHANGED=src.LASTCHANGED
|
|
,CREATED=src.CREATED
|
|
,MODIFIER=src.MODIFIER
|
|
,CREATOR=src.CREATOR
|
|
,ELOZOTANEVIREKORDID=src.ELOZOTANEVIREKORDID
|
|
,NNID=src.NNID
|
|
|
|
WHEN NOT MATCHED BY target THEN
|
|
INSERT(ID,C_VALUE,C_NAME,C_NAME_1,C_NAME_2,C_NAME_3,C_NAME_4,C_VISIBLE,C_CODE,C_TYPE,C_PROTECTED,C_COLOR,C_ORDER,C_BGCOLOR,C_DESCRIPTION,C_ISACTIVE,C_SHORTNAME,C_DICTIONARYTYPEID,C_INTEZMENYID,C_TANEVID,TOROLT,SERIAL,LASTCHANGED,CREATED,MODIFIER,CREATOR,ELOZOTANEVIREKORDID,NNID)
|
|
VALUES(ID,C_VALUE,C_NAME,C_NAME_1,C_NAME_2,C_NAME_3,C_NAME_4,C_VISIBLE,C_CODE,C_TYPE,C_PROTECTED,C_COLOR,C_ORDER,C_BGCOLOR,C_DESCRIPTION,C_ISACTIVE,C_SHORTNAME,C_DICTIONARYTYPEID,C_INTEZMENYID,@pNextTanevId,TOROLT,0,GETDATE(),GETDATE(),MODIFIER,CREATOR,ID,NNID);
|
|
SET IDENTITY_INSERT T_DICTIONARYITEMBASE OFF
|
|
--Item Base Merge END--
|
|
--Item Type Merge Start--
|
|
SET @typeSQL='
|
|
MERGE INTO T_'+@typeName+' AS trg
|
|
USING (SELECT TOP 1 *
|
|
FROM T_'+@typeName+' type
|
|
WHERE type.ID='+cast(@pItemId as varchar)+' AND type.C_ALINTEZMENYID='+cast(@pIntezmenyId as varchar)+' AND type.C_ALTANEVID='+cast(@pCurrentTanevId as varchar)+') AS src
|
|
ON trg.ID=src.ID AND trg.C_ALTANEVID='+cast(@pNextTanevId as varchar)+' AND src.C_ALTANEVID='+cast(@pCurrentTanevId as varchar)+' AND trg.C_ALINTEZMENYID=src.C_ALINTEZMENYID
|
|
WHEN MATCHED THEN
|
|
UPDATE SET TOROLT=src.TOROLT
|
|
WHEN NOT MATCHED BY target THEN
|
|
INSERT(ID,C_ALINTEZMENYID,C_ALTANEVID,TOROLT,SERIAL,LASTCHANGED,CREATED,MODIFIER,CREATOR,NNID,ELOZOTANEVIREKORDIDA,ELOZOTANEVIREKORDID)
|
|
VALUES(ID,C_ALINTEZMENYID,'+cast(@pNextTanevId as varchar)+',TOROLT,0,GETDATE(),GETDATE(),MODIFIER,CREATOR,NNID,ELOZOTANEVIREKORDIDA,ID);'
|
|
exec sp_executesql @typeSQL
|
|
--Item Type Merge End--
|
|
END |