USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPGREATESTNEEDCOPY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SPONSORSHIPGREATESTNEEDCOPY(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
      @NEWID uniqueidentifier = null output
)
as

    set nocount on;
  declare @CHANGEAGENTID uniqueidentifier;
  declare @NAME nvarchar(255);
  declare @INDEX int;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 1;
    set @TSLONG = 0;
  set @NEWID = newid();

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

  select 
    @NAME= NAME 
  from dbo.SPONSORSHIPGREATESTNEEDRULESET
  where ID=@ID

  select
    @INDEX = count(*)
  from dbo.SPONSORSHIPGREATESTNEEDRULESET
  where NAME like 'Copy Of '+ @NAME +'%'

  insert into dbo.SPONSORSHIPGREATESTNEEDRULESET(ID,NAME,SPONSORSHIPOPPORTUNITYTYPECODE,RULESETDATA,ADDEDBYID,CHANGEDBYID)
    select
    @NEWID, 
    'Copy Of ' + NAME + case when @INDEX = 0 then '' else ' ('+ convert(nvarchar(255),(@INDEX + 1)) +')' end,
    SPONSORSHIPOPPORTUNITYTYPECODE,
    RULESETDATA,
    @CHANGEAGENTID,
    @CHANGEAGENTID
  from dbo.SPONSORSHIPGREATESTNEEDRULESET
  where ID = @ID

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    select
        @DATALOADED,
    @NEWID

    return 0;