96 lines
2.9 KiB
Transact-SQL
96 lines
2.9 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS sp_ImportTermek
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_ImportTermek
|
|
@xml xml,
|
|
@userID int,
|
|
@intezmenyID int,
|
|
@tanevID int,
|
|
@mukodesiHelyId int
|
|
AS
|
|
BEGIN
|
|
|
|
BEGIN TRY
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
|
|
DECLARE @tbl TABLE (
|
|
C_JELLEG int,
|
|
C_KAPACITAS int,
|
|
C_NEV nvarchar(50),
|
|
C_TERULET int,
|
|
C_TOBBORATLEHETTARTANI char(1),
|
|
C_VIZSGAKAPACITAS int,
|
|
C_MUKODESIHELYID int,
|
|
C_TEREMFELELOSID int
|
|
)
|
|
|
|
INSERT INTO @tbl
|
|
SELECT
|
|
sor.value('number((JellegId)[1])', 'int') AS C_JELLEG,
|
|
sor.value('xs:decimal((Kapacitas)[1])', 'int') AS C_KAPACITAS,
|
|
sor.value('(TeremNev)[1]', 'nvarchar(50)') AS C_NEV,
|
|
sor.value('number((Terulet)[1])', 'int') AS C_TERULET,
|
|
sor.value('(tobbora)[1]', 'char(1)') AS C_TOBBORATLEHETTARTANI,
|
|
sor.value('number((vizsgakapacitas)[1])', 'int') AS C_VIZSGAKAPACITAS,
|
|
@mukodesiHelyId AS C_MUKODESIHELYID,
|
|
sor.value('number((teremfelelos)[1])', 'int') AS C_TEREMFELELOSID
|
|
FROM @xml.nodes('/TeremImport/TeremLista/Terem') as sorok(sor)
|
|
|
|
BEGIN TRANSACTION
|
|
UPDATE t
|
|
SET
|
|
t.C_JELLEG = IsNull(s.C_JELLEG, t.C_JELLEG)
|
|
,t.C_KAPACITAS = IsNull(s.C_KAPACITAS, t.C_KAPACITAS)
|
|
,t.C_NEV = IsNull(s.C_NEV, t.C_NEV)
|
|
,t.C_TERULET = IsNull(s.C_TERULET, t.C_TERULET)
|
|
,t.C_TOBBORATLEHETTARTANI = IsNull(s.C_TOBBORATLEHETTARTANI, ISNULL(t.C_TOBBORATLEHETTARTANI,'F'))
|
|
,t.C_VIZSGAKAPACITAS = IsNull(s.C_VIZSGAKAPACITAS, t.C_VIZSGAKAPACITAS)
|
|
,t.C_MUKODESIHELYID = IsNull(s.C_MUKODESIHELYID, t.C_MUKODESIHELYID)
|
|
,t.C_TEREMFELELOSID = IsNull(s.C_TEREMFELELOSID, t.C_TEREMFELELOSID)
|
|
,t.SERIAL = t.SERIAL + 1
|
|
,t.LASTCHANGED = GETDATE()
|
|
,t.MODIFIER = @userID
|
|
FROM T_TEREM t
|
|
INNER JOIN @tbl s ON s.C_NEV = t.C_NEV AND s.C_MUKODESIHELYID = t.C_MUKODESIHELYID AND t.C_INTEZMENYID = t.C_INTEZMENYID
|
|
|
|
INSERT INTO T_TEREM (
|
|
C_AKTIV
|
|
,C_JELLEG
|
|
,C_KAPACITAS
|
|
,C_NEV
|
|
,C_TERULET
|
|
,C_TOBBORATLEHETTARTANI
|
|
,C_VIZSGAKAPACITAS
|
|
,C_MUKODESIHELYID
|
|
,C_TEREMFELELOSID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
'T' AS C_ACTIVE
|
|
,C_JELLEG AS C_JELLEG
|
|
,C_KAPACITAS AS C_KAPACITAS
|
|
,C_NEV AS C_NEV
|
|
,C_TERULET AS C_TERULET
|
|
,ISNULL(C_TOBBORATLEHETTARTANI,'F') AS C_TOBBORATLEHETTARTANI
|
|
,C_VIZSGAKAPACITAS AS C_VIZSGAKAPACITAS
|
|
,C_MUKODESIHELYID AS C_MUKODESIHELYID
|
|
,C_TEREMFELELOSID AS C_TEREMFELELOSID
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@tanevID AS C_TANEVID
|
|
,NULL AS MODIFIER
|
|
,@userID AS CREATOR
|
|
FROM @tbl t
|
|
WHERE NOT EXISTS (SELECT 1 FROM T_TEREM WHERE C_NEV = t.C_NEV AND C_MUKODESIHELYID = t.C_MUKODESIHELYID)
|
|
COMMIT TRANSACTION
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW
|
|
END CATCH
|
|
END
|
|
|