第四章 WF3.x持久化存储过程.docxVIP

setANSI_NULLSONsetQUOTED_IDENTIFIERONGOALTERProcedure [dbo].[InsertInstanceState]@uidInstanceID uniqueidentifier,@state image,@status int,@unlocked int,@blocked int,@info ntext,@ownerID uniqueidentifier=NULL,@ownedUntil datetime=NULL,@nextTimer datetime,@result intoutput,@currentOwnerID uniqueidentifieroutputAsdeclare @localized_string_InsertInstanceState_Failed_Ownership nvarchar(256)set @localized_string_InsertInstanceState_Failed_Ownership = N实例所有权冲突set @result = 0set @currentOwnerID = @ownerIDdeclare @now datetimeset @now =GETUTCDATE()SETTRANSACTIONISOLATIONLEVELREAD COMMITTEDsetnocountonIF @status=1 OR @status=3BEGINDELETEFROM [dbo].[InstanceState] WHERE uidInstanceID=@uidInstanceID AND((ownerID = @ownerID AND ownedUntil=@now)OR(ownerID ISNULLAND @ownerID ISNULL))if(@@ROWCOUNT= 0 )beginset @currentOwnerID =NULLselect @currentOwnerID=ownerID from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceIDif( @currentOwnerID ISNOTNULL)begin-- cannot delete the instance state because of an ownership conflict-- RAISERROR(@localized_string_InsertInstanceState_Failed_Ownership, 16, -1)set @result =-2returnendendelseBEGINDELETEFROM [dbo].[CompletedScope] WHERE uidInstanceID=@uidInstanceIDendENDELSEBEGINifnotexists(Select 1 from [dbo].[InstanceState] Where uidInstanceID = @uidInstanceID )BEGIN--Insert OperationIF @unlocked = 0beginInsertinto [dbo].[InstanceState] Values(@uidInstanceID,@state,@status,@unlocked,@blocked,@info,@now,@ownerID,@ownedUntil,@nextTimer)endelsebeginInsertinto [dbo].[InstanceState] Values(@uidInstanceID,@state,@status,@unlocked,@blocked,@info,@now,null,null,@nextTimer)endENDELSEBEGINIF @unlocked = 0beginUpdate [dbo].[InstanceState] Set state = @state,status = @status,unlocked = @unlocked,blocked = @blocked,info = @info,modified = @now,ownedUntil = @ownedUntil,nextTimer = @nextTimerWhere uidInstanceID = @uidInstanceID AND((ownerID = @ownerID AND ownedUntil=@now)OR(ownerID ISNULLAND @ownerID ISNULL))if(@@ROWCOUNT= 0 )BEGIN-- RAISERROR(@localized_s