# Tuesday, 08 May 2012
« Visual Studio 11 Beta lacking support fo... | Main | TFS / MSBuild error related to ServiceDe... »

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

Tuesday, 08 May 2012 10:54:16 (Pacific Daylight Time, UTC-07:00)  #    Disclaimer  |  Comments [0]  | 
Comments are closed.