spTransactionsSignup_InsertProfileUpdate

CREATE       procedure dbo.spTransactionsSignup_InsertProfileUpdate
    @ClientID int,
    @UserID int = NULL,
    @xmlData ntext
    as

    declare @ret int
    set @ret=0

    --profileupdate can only be done by a logged in user
    --Comment out to remain backwards compatible with existing signup that does not use UserID
    /*if (@UserID is null) or (@UserID=0) begin
        RAISERROR ('Unable to insert transaction - Profile Updates can only be done by a registered user',16,2)
        return 0            
    end*/        

    if datalength(@xmlData)<8 begin
       RAISERROR ('Unable to insert transaction - ShelbySignupTran.XMLObjectData cannot be a zero-length string and must be well formed XML',16,1)
       return 0
    end


    declare @lock_result int

    BEGIN TRAN

    --use app lock to sync the block below
    exec @lock_result=sp_getAppLock @Resource='Shelby.spTransactionsSignup_InsertProfileUpdate' ,@LockMode='Exclusive',@LockTimeOut=30000

    IF @lock_result < 0
    BEGIN
        ROLLBACK TRAN
        return 0
    END
    ELSE
    BEGIN

        --see if pending update exists, if so just update it.
        declare @id int
        select Top 1 @id= SignupTransactionsID from dbo.SignupTransactions
        WHERE
         clientsID = @ClientID and UserID = @UserID
         and processed_date is null
        order by DateLastChanged desc


        if not @id is null 
            begin
             update dbo.SignupTransactions set DateLastChanged=getutcdate(),
            processed_date = null,XMLObjectData=@xmlData, UserID = @UserID
                    where SignupTransactionsID=@id
            set @ret=@id
            end
        else
            begin

            insert into dbo.SignupTransactions
            (clientsid,UserID,XMLObjectData) 
            values
            (@clientid,@UserID,@xmlData)

            if @@error =0 set @ret= @@identity
            else set @ret= 0


        end


        --This will save the transaction into the BBNCTransactions Table
        Declare @BBNCTransactionsPKID int
        Set @BBNCTransactionsPKID = 0

        Declare @TransactionType nvarchar(100)
        Set @TransactionType = '{5F84002B-ABB1-4f50-A244-D4B14FBB1579}'

        exec spTransactions_InsertTransaction @BBNCTransactionsPKID, @ret, @TransactionType, @XMLData, @UserID, 0


        exec @lock_result=sp_releaseAppLock @resource='Shelby.spTransactionsSignup_InsertProfileUpdate'

        COMMIT TRAN

        return @ret

    END