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)
CREATE PROCEDURE [dbo].[UpdateOrAddOrder]
@emailAddress NVARCHAR(255)
,@order XML
AS
BEGIN TRY
DECLARE @userId INT
BEGIN TRAN
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
DECLARE @tmpOrder TABLE
([itemId] UNIQUEIDENTIFIER
,[itemName] NVARCHAR(25)
,[itemQuantity] INT
,[itemOrderDate] DATE)
INSERT INTO @tmpOrder
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)
UPDATE [dbo].[Order]
SET
[Order].[Quantity] = [t].[itemQuantity],
[Order].[ModifiedOn] = GETUTCDATE()
FROM
@tmpOrder AS [t]
WHERE
[Order].[customerId] = @customerId AND
[t].[itemId] = [Order].[itemId]
INSERT INTO [dbo].[Order]([customerId], [itemId], [itemName], [itemOrderDate])
SELECT
@customerId
,[t].[itemId]
,[t].[itemName]
,[t].[itemOrderDate]
FROM
@tmpOrder AS [t]
WHERE [t].[itemId] NOT IN
(SELECT [itemId] FROM [dbo].[Order] WHERE customerId = @customerId)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
RETURN
END CATCH
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