USP_CHANGEAGENT_GETORCREATECHANGEAGENT

CREATE procedure dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT

@ID uniqueidentifier =null output,
@APPNAME nvarchar(200)='',
@USERNAME nvarchar(128)='',
@PROCESSNAME nvarchar(100) = ''

as

set nocount on;

if len(coalesce(@APPNAME,''))=0 set @APPNAME   = App_Name();
--if len(coalesce(@USERNAME,''))=0 set @USERNAME = SESSION_SUSER() --SESSION_USER
if len(coalesce(@USERNAME,''))=0 set @USERNAME = ORIGINAL_LOGIN() ;

-- sometimes, SQL Windows in Management Studio don't set App_Name() properly, and it comes back empty.  To
-- facilitate in-house debugging, make sure APPNAME is set.
if len(coalesce(@APPNAME,''))=0 set @APPNAME   = 'Unknown Application';

if @PROCESSNAME is null set @PROCESSNAME = '';

set @ID = null;

--Assume it exists, do this outside the lock stuff below
select @ID=ID
from dbo.CHANGEAGENT
where 
    APPLICATIONNAME=@APPNAME 
    and USERNAME=@USERNAME
      and PROCESSNAME=@PROCESSNAME
      and [SERVERNAME]=@@SERVERNAME; --pdg 8.21.2008 added @@SERVERNAME to support replication for PCI/BBPS

if @ID is not NULL return 0;



/*
Need a lock to prevent a race condition where between the time of the SELECT below
and if another connection for the same username/appname adds a row.  So begin a transaction and use an app lock.  Note that
regardless of what happens the transaction is committed because there is no need to RollBack
the effect of this operation and this will work better with nested transactions (sine a Rollback would
also rollback any outer transactions as well. We just need a transaction to limit the scope of this lock and not have
it outlive the intended scope below.
*/
declare @r int;
declare @lockName nvarchar(500);

set @lockName='USP_CHANGEAGENT_GETORCREATECHANGEAGENT.' + @USERNAME + '.' + @APPNAME + '.' + @PROCESSNAME;

BEGIN TRY

BEGIN TRANSACTION;

exec @r = sp_getapplock 
                        @Resource = @lockName,
                        @LockMode = 'Exclusive',
                        @LockOwner= 'Transaction',
                        @LockTimeout=10000;

if @r <0 
BEGIN
      RAISERROR('Timed out waiting for app lock in USP_CHANGEAGENT_GETORCREATECHANGEAGENT',16,10);
      COMMIT TRANSACTION;
      RETURN @r;
END

select @ID=ID
from dbo.CHANGEAGENT
where 
    APPLICATIONNAME=@APPNAME 
    and USERNAME=@USERNAME
      and PROCESSNAME=@PROCESSNAME
      and [SERVERNAME]=@@SERVERNAME; --pdg 8.21.2008 added @@SERVERNAME to support replication for PCI/BBPS

if @ID is null 
      begin
          set @ID=NewID();


          insert into dbo.CHANGEAGENT (ID,APPLICATIONNAME,USERNAME,PROCESSNAME)
          values (@ID,@APPNAME,@USERNAME,@PROCESSNAME);
      end

--release lock
exec @r = sp_releaseapplock @Resource =@lockName ,@LockOwner= 'Transaction';

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
      COMMIT TRANSACTION;
      exec dbo.USP_RAISE_ERROR;
      set @ID=null;
      return 1;
END CATCH

return 0;