USP_COPY_ACTIONITEM

CREATE procedure dbo.USP_COPY_ACTIONITEM
( @ID uniqueidentifier = null output,
  @OLDACTIONITEMID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
 ) with execute as caller

as
BEGIN
    SET NOCOUNT ON

    if @ID is null
    set @ID = newid()

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    declare @NAME nvarchar(250)
    declare @i int
    declare @l int
    declare @FOLDERID int

    set @CURRENTDATE = getdate()
    SET @i = 1

    select @FOLDERID = FOLDERID FROM dbo.ACTIONITEMFOLDER WHERE ACTIONITEMID = @OLDACTIONITEMID

    Set @FOLDERID = ISNULL(@FOLDERID,0)

    SELECT @NAME = NAME + ' COPY '
    FROM dbo.ACTION_ITEM WHERE ID = @OLDACTIONITEMID

    set @l = len(@NAME)

    WHILE EXISTS (SELECT 1 FROM dbo.ACTION_ITEM WHERE NAME = @NAME)
    BEGIN
        SET @NAME = substring(@NAME,1,@l) + CAST(@i as varchar(4))
        SET @i = @i+1
    END



    begin try

    -- handle inserting the data


        INSERT INTO dbo.ACTION_ITEM    (ID, NAME, SYNOPSIS, DESCRIPTION, TYPECODE, STATUSCODE, TOPICCODE, STARTDATE, ENDDATE, ISEMAILDELIVERY, ISFAXDELIVERY, ISLETTERDELIVERY, ISDELIVERYNONE, ISFAXBACKUPFOREMAILSENABLED, ISEMAILBACKUPFORFAXENABLED, TARGETOPTIONCODE, TARGETDATABASE, CONFIRMATIONTEXT, RESULTTEXT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        SELECT @ID, @NAME, SYNOPSIS, DESCRIPTION, TYPECODE, 0, TOPICCODE, STARTDATE, ENDDATE, ISEMAILDELIVERY, ISFAXDELIVERY, ISLETTERDELIVERY, ISDELIVERYNONE, ISFAXBACKUPFOREMAILSENABLED, ISEMAILBACKUPFORFAXENABLED, TARGETOPTIONCODE, TARGETDATABASE, CONFIRMATIONTEXT, RESULTTEXT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        FROM dbo.ACTION_ITEM WHERE ID = @OLDACTIONITEMID

    -- add it to folder


        EXEC USP_DATAFORMTEMPLATE_ADD_ACTION_ITEM_FOLDER @ACTIONITEMID = @ID, @FOLDERID = @FOLDERID

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0            

END