spTransactions_InsertProfileOptInOutUpdate

CREATE   PROCEDURE [dbo].[spTransactions_InsertProfileOptInOutUpdate]
(
    @ClientID int,
    @UserID int,
  @BackOfficeID int,
    @xmlData ntext
)
AS

DECLARE @retVal int
SET @retVal=0

IF (@UserID IS NULL) OR (@UserID<1) BEGIN
    SET @UserID = NULL
END
IF (@BackOfficeID IS NULL) OR (@BackOfficeID<1) BEGIN
    SET @BackOfficeID = NULL
END


IF DATALENGTH(@xmlData)<8 BEGIN
    RAISERROR ('Unable to insert transaction - ProfileOptInOutUpdateTransactions.XMLObjectData cannot be a zero-length string and must be well formed XML',16,1)
    RETURN 0
END

DECLARE @lockResult int

BEGIN TRANSACTION

--use app lock to sync the block below
EXEC @lockResult=sp_getAppLock @Resource='Shelby.spTransactions_InsertProfileOptingUpdate' ,@LockMode='Exclusive',@LockTimeOut=30000

IF @lockResult < 0 BEGIN
    ROLLBACK TRANSACTION
    RETURN 0
END
ELSE BEGIN
    --see if pending update exists, and if so just update it.
    DECLARE @profileTransactionID int
    SELECT TOP 1 @profileTransactionID=[ProfileUpdateTransactionsID] FROM [dbo].[ProfileUpdateTransactions]
    WHERE [ClientsID] = @ClientID AND [AddedByUserID] = @UserID AND [processed_date] IS NULL
    AND [BackOfficeID] = @BackOfficeID
    ORDER BY [DateLastChanged] DESC

    IF NOT @profileTransactionID IS NULL
        BEGIN
            UPDATE [dbo].[ProfileUpdateTransactions]
            SET [BackOfficeID] = @BackOfficeID,
            [DateLastChanged]=getutcdate(),
            [processed_date] = NULL,
            [XMLObjectData]=@xmlData
            WHERE [ProfileUpdateTransactionsID]=@profileTransactionID

            SET @retVal=@profileTransactionID
        END
    ELSE
        BEGIN
            INSERT INTO [dbo].[ProfileUpdateTransactions]
            ([ClientsID],[AddedByUserID],[BackOfficeID],[XMLObjectData])
            VALUES (@ClientID,@UserID,@BackOfficeID,@xmlData)

            IF @@ERROR=0 SET @retVal=@@IDENTITY
            ELSE SET @retVal=0
        END

    EXEC @lockResult=sp_releaseAppLock @resource='Shelby.spTransactions_InsertProfileOptingUpdate'

    COMMIT TRANSACTION
    RETURN @retVal
END