98 lines
2.9 KiB
Transact-SQL
98 lines
2.9 KiB
Transact-SQL
IF OBJECT_ID('sp_ImportTanmenetek', 'P') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_ImportTanmenetek
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_ImportTanmenetek
|
|
@xml xml,
|
|
@tantargyID int,
|
|
@osztalycsoportID int,
|
|
@userID int,
|
|
@intezmenyID int,
|
|
@tanevID int
|
|
AS
|
|
BEGIN
|
|
|
|
BEGIN TRY
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
|
|
DECLARE @tbl TABLE (
|
|
C_ORASZAM int,
|
|
C_TEMA nvarchar(max)
|
|
)
|
|
|
|
INSERT INTO @tbl
|
|
SELECT
|
|
sor.value('(oraszam)[1]', 'int') AS C_ORASZAM,
|
|
sor.value('(tema)[1]', 'nvarchar(1000)') AS C_TEMA
|
|
FROM @xml.nodes('/TanmenetImport/TanmenetLista/Tanmenet') as sorok(sor)
|
|
|
|
BEGIN TRANSACTION
|
|
UPDATE m SET
|
|
m.C_NEV = LEFT(t.C_TEMA, 1000) -- nvarchar(1000)
|
|
,m.C_TEMA = t.C_TEMA -- nvarchar(MAX)
|
|
,m.SERIAL = m.SERIAL + 1 -- int
|
|
,m.LASTCHANGED = GETDATE() -- datetime
|
|
,m.MODIFIER = @userID -- int
|
|
FROM T_TANMENET m
|
|
INNER JOIN @tbl t ON t.C_ORASZAM = m.C_ORASZAM
|
|
AND m.C_TANTARGYID = @tantargyID
|
|
AND C_OSZTALYCSOPORTID = @osztalycsoportID
|
|
AND C_FELTOLTOID = @userID
|
|
|
|
INSERT INTO T_TANMENET (
|
|
C_BEJEGYZESIDEJE
|
|
,C_MEGJEGYZES
|
|
,C_NEV
|
|
,C_ROVIDNEV
|
|
,C_TEMA
|
|
,C_ORASZAM
|
|
,C_TANTARGYID
|
|
,C_OSZTALYCSOPORTID
|
|
,C_FELTOLTOID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
) SELECT
|
|
GETDATE() AS C_BEJEGYZESIDEJE -- C_BEJEGYZESIDEJE - datetime
|
|
,NULL AS C_MEGJEGYZES -- C_MEGJEGYZES - nvarchar(255)
|
|
,LEFT(t.C_TEMA, 1000) AS C_NEV -- C_NEV - nvarchar(1000)
|
|
,NULL AS C_ROVIDNEV -- C_ROVIDNEV - nvarchar(255)
|
|
,t.C_TEMA AS C_TEMA -- C_TEMA - nvarchar(MAX)
|
|
,t.C_ORASZAM AS C_ORASZAM -- C_ORASZAM - int
|
|
,@tantargyID AS C_TANTARGYID -- C_TANTARGYID - int
|
|
,@osztalycsoportID AS C_OSZTALYCSOPORTID -- C_OSZTALYCSOPORTID - int
|
|
,@userID AS C_FELTOLTOID -- C_FELTOLTOID - int
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@tanevID AS C_TANEVID
|
|
,'F' AS TOROLT -- TOROLT - char(1)
|
|
,1 AS SERIAL -- SERIAL - int
|
|
,NULL AS LASTCHANGED -- LASTCHANGED - datetime
|
|
,GETDATE() AS CREATED -- CREATED - datetime
|
|
,NULL AS MODIFIER -- MODIFIER - int
|
|
,@userID AS CREATOR -- CREATOR - int
|
|
FROM @tbl t
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM T_TANMENET
|
|
WHERE T_TANMENET.C_ORASZAM = t.C_ORASZAM
|
|
AND C_TANTARGYID = @tantargyID
|
|
AND C_OSZTALYCSOPORTID = @osztalycsoportID
|
|
AND C_FELTOLTOID = @userID
|
|
)
|
|
COMMIT TRANSACTION
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW
|
|
END CATCH
|
|
END
|
|
GO
|
|
|