I came across a stored procedure that was doing the upsert (update/insert) pattern and it was using XML as the source rather than a TVP. Since we're using Entity Framework and TVP isn't reaily a vaiable option at this point, I decided to see if I could re-write it to work on SQL Azure as a T-SQL Merge.
The original looked something like this (schema and variable names changed to protect the innocent)
After some digging around, I came up with this:
CREATE PROCEDURE [dbo].[UpdateOrAddOrder] (@emailAddress NVARCHAR(255) ,@order XML) AS BEGIN TRY BEGIN TRAN DECLARE @ErrMsg NVARCHAR (4000) DECLARE @ErrSeverity INT DECLARE @subscriberId INT
IF NOT EXISTS(SELECT (1) FROM [dbo].[Customer] WHERE [emailAddress] = @emailAddress) BEGIN INSERT INTO [dbo].[Customer] VALUES(@emailAddress, GETUTCDATE()) SELECT @customerId = SCOPE_IDENTITY() END ELSE SELECT @customerId = [customerId] FROM [dbo].[Customer] WHERE [emailAddress] = @emailAddress
MERGE [dbo].[Order] AS target USING (SELECT i.value('(Identifier)[1]', 'UNIQUEIDENTIFIER') AS 'itemId' ,i.value('(Name)[1]', 'VARCHAR (25)') AS 'itemName' ,i.value('(Quantity)[1]', 'INT') AS 'itemQuantity' ,i.value('(OrderDate)[1]', 'DATE') AS 'itemOrderDate' FROM @order.nodes('/Order/Item') AS o(i)) AS source ([itemId] ,[itemName] ,[itemQuantity] ,[itemOrderDate]) ON (target.[itemId] = source.[itemId] AND target.[customerId] = @customerId) WHEN MATCHED THEN UPDATE SET target.[itemQuantity] = source.[itemQuantity] ,target.[ModifiedOn] = GETUTCDATE() WHEN NOT MATCHED THEN INSERT ([customerId] ,[itemId] ,[itemName] ,[itemQuantity] ,[itemOrderDate]) VALUES (@customerId ,source.[itemId] ,source.[itemName] ,source.[itemQuantity] ,source.[itemOrderDate]);
COMMIT TRAN RETURN END TRY
BEGIN CATCH ROLLBACK TRAN SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) RETURN END CATCH
Powered by: newtelligence dasBlog 2.3.9074.18820
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2024, Demitrius Nelon
E-mail