Voorraadsynchronisatie met MERGE — de gevaarlijkste tien regels T-SQL
Eén MERGE-statement dat invoegt, bijwerkt én verwijdert, met een dry-run en een CATCH-blok. Er zit dode code in, een dataverliesrisico en een subtiele reden waarom HOLDLOCK er staat.
MERGE is het statement waarmee je in één keer invoegt, bijwerkt en verwijdert. Het is ook het
statement met de meeste voetangels in SQL Server. Deze procedure synchroniseert de voorraad van één
leverancier vanuit een stagingtabel en gebruikt vrijwel alles wat MERGE te bieden heeft.
Er zit één stuk dode code in, één constructie die dataverlies kan veroorzaken bij slechte invoer, en één hint die er niet voor de snelheid staat. Zoek ze voordat je de antwoorden opent.
CREATE OR ALTER PROCEDURE dbo.usp_SynchroniseerVoorraad
@LeverancierId INT,
@BatchId UNIQUEIDENTIFIER,
@DryRun BIT = 0
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @Toegevoegd INT = 0, @Gewijzigd INT = 0, @Verwijderd INT = 0;
DECLARE @Log TABLE (
Actie NVARCHAR(10),
ProductId INT,
OudAantal INT,
NieuwAantal INT
);
BEGIN TRY
BEGIN TRANSACTION;
;WITH Aangeleverd AS (
SELECT s.ProductId,
SUM(s.Aantal) AS Aantal,
MAX(s.AangeleverdOp) AS AangeleverdOp,
ROW_NUMBER() OVER (PARTITION BY s.ProductId
ORDER BY MAX(s.AangeleverdOp) DESC) AS rn
FROM staging.LeveranciersVoorraad s
WHERE s.LeverancierId = @LeverancierId
AND s.BatchId = @BatchId
AND s.Aantal >= 0
GROUP BY s.ProductId
)
MERGE dbo.Voorraad WITH (HOLDLOCK) AS doel
USING (SELECT ProductId, Aantal, AangeleverdOp
FROM Aangeleverd
WHERE rn = 1) AS bron
ON doel.ProductId = bron.ProductId
AND doel.LeverancierId = @LeverancierId
WHEN MATCHED AND doel.Aantal <> bron.Aantal THEN
UPDATE SET doel.Aantal = bron.Aantal,
doel.BijgewerktOp = bron.AangeleverdOp
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductId, LeverancierId, Aantal, BijgewerktOp)
VALUES (bron.ProductId, @LeverancierId, bron.Aantal, bron.AangeleverdOp)
WHEN NOT MATCHED BY SOURCE AND doel.LeverancierId = @LeverancierId THEN
DELETE
OUTPUT $action,
ISNULL(inserted.ProductId, deleted.ProductId),
deleted.Aantal,
inserted.Aantal
INTO @Log (Actie, ProductId, OudAantal, NieuwAantal);
SELECT @Toegevoegd = SUM(CASE WHEN Actie = 'INSERT' THEN 1 ELSE 0 END),
@Gewijzigd = SUM(CASE WHEN Actie = 'UPDATE' THEN 1 ELSE 0 END),
@Verwijderd = SUM(CASE WHEN Actie = 'DELETE' THEN 1 ELSE 0 END)
FROM @Log;
IF @DryRun = 1
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
SELECT @Toegevoegd AS Toegevoegd,
@Gewijzigd AS Gewijzigd,
@Verwijderd AS Verwijderd;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
INSERT INTO dbo.SyncFouten (LeverancierId, BatchId, Foutmelding, OpgetredenOp)
VALUES (@LeverancierId, @BatchId, ERROR_MESSAGE(), SYSUTCDATETIME());
THROW;
END CATCH
END;
Wat doet WHEN NOT MATCHED BY SOURCE, en waarom staat er AND doel.LeverancierId = @LeverancierId
achter?
WITH (HOLDLOCK) staat op de doeltabel. Dat is geen performancehint. Waar dient hij voor?
Er zit een ROW_NUMBER() OVER (PARTITION BY s.ProductId ...) in de CTE, en de bron filtert op rn = 1.
Wat doet dat filter in de praktijk?
De bronfilter bevat AND s.Aantal >= 0. Wat gebeurt er met een product waarvan de leverancier in deze
batch uitsluitend negatieve aantallen aanlevert?
Bij @DryRun = 1 wordt de transactie teruggedraaid. Toch bevat de daaropvolgende SELECT nog de juiste
aantallen. Hoe kan dat?
In het CATCH-blok staat IF XACT_STATE() <> 0 in plaats van IF @@TRANCOUNT > 0. Waarom, gegeven
SET XACT_ABORT ON bovenaan?
De batch bevat geen enkele wijziging: alle aantallen zijn al gelijk, er is niets toe te voegen of te verwijderen. Wat retourneert de procedure?
De OUTPUT-clausule gebruikt ISNULL(inserted.ProductId, deleted.ProductId) voor het product, maar
schrijft deleted.Aantal en inserted.Aantal rechtstreeks weg. Waarom is dat verschil er, en is het
correct?