64 lines
2.3 KiB
Transact-SQL
64 lines
2.3 KiB
Transact-SQL
-- =============================================
|
|
-- Author: <Kezdi Hunor>
|
|
-- Create date: <2019 05 14>
|
|
-- Description: <Felupdateli az adott renszerbeallitas (multiselectionlist) jsonjet a dictionarytype (adatszotar) szerint>
|
|
-- =============================================
|
|
DROP PROCEDURE IF EXISTS uspUpdateRendszerbeallitasokMultiSelectionList
|
|
GO
|
|
|
|
CREATE PROCEDURE uspUpdateRendszerbeallitasokMultiSelectionList
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pBeallitasTipusId int
|
|
,@pDictionaryTypeId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @OptionList nVARCHAR(MAX)
|
|
,@json NVARCHAR(MAX)
|
|
,@json2 NVARCHAR(MAX)
|
|
,@BeallitasTipusName nVARCHAR(MAX)
|
|
|
|
CREATE TABLE #tmp (options nVARCHAR(MAX))
|
|
CREATE TABLE #tmpjson (jsonKey nvarchar(100), jsonValue nvarchar(max))
|
|
|
|
SET @json = (
|
|
SELECT C_ERTEK
|
|
FROM T_RENDSZERBEALLITAS_OSSZES
|
|
WHERE C_BEALLITASTIPUS = @pBeallitasTipusId
|
|
AND C_INTEZMENYID = @pIntezmenyId
|
|
AND C_TANEVID = @pTanevId
|
|
AND TOROLT = 'F')
|
|
INSERT INTO #tmpjson
|
|
SELECT [key], [value]
|
|
FROM OPENJSON(@json)
|
|
SET @json2 = (
|
|
SELECT jsonValue
|
|
FROM #tmpjson
|
|
WHERE jsonKey='Options')
|
|
SET @BeallitasTipusName = (
|
|
SELECT jsonValue
|
|
FROM #tmpjson
|
|
WHERE jsonKey='Id')
|
|
|
|
INSERT INTO #tmp
|
|
SELECT '{"Selected":'+IIF((ISNULL(ID,'') <> '' AND EXISTS(SELECT SelectedID FROM OPENJSON (@json2) WITH (Selected nvarchar(100) '$.Selected',Text nvarchar(500) '$.Text', SelectedID int '$.Value' ) WHERE Selected = 'true' AND SelectedID = ID)),'true','false')
|
|
+ ',"Text":"' + ISNULL(REPLACE(C_NAME, '"', '\"'),'')
|
|
+ '","Value":"' + CAST(ISNULL(ID,'') as nvarchar(10)) + '"}'
|
|
FROM T_DICTIONARYITEMBASE_OSSZES
|
|
WHERE C_DICTIONARYTYPEID = @pDictionaryTypeId
|
|
AND C_INTEZMENYID = @pIntezmenyId
|
|
AND C_TANEVID = @pTanevId
|
|
AND TOROLT = 'F'
|
|
ORDER BY C_VALUE
|
|
|
|
SET @OptionList = null
|
|
SELECT @OptionList = COALESCE(@OptionList + ', ', '') + options FROM #tmp
|
|
SET @OptionList = ISNULL(@OptionList, '')
|
|
UPDATE T_RENDSZERBEALLITAS_OSSZES SET
|
|
C_ALAPERTEK = '{"Id":"'+@BeallitasTipusName+'","Options":[' + @OptionList + ']}',
|
|
C_ERTEK = '{"Id":"'+@BeallitasTipusName+'","Options":[' + @OptionList + ']}'
|
|
WHERE C_BEALLITASTIPUS = @pBeallitasTipusId AND C_INTEZMENYID = @pIntezmenyId AND C_TANEVID = @pTanevId AND TOROLT = 'F'
|
|
END
|
|
GO
|
|
|