USP_SPONSOR_FAF_BENEFIT_ADD

    create procedure dbo.USP_SPONSOR_FAF_BENEFIT_ADD
    (
                    @SPONSORID uniqueidentifier,
                    @BENEFITID uniqueidentifier,
                    @EVENTPRICEID uniqueidentifier = null,
                    @BENEFITTYPECODE tinyint = 0,
                    @BENEFITOPTIONIDLIST varchar(4000) = '',
                    @PREFERENCELIST varchar(4000) = '',
                    @QUANTITY int = 1,
                    @CHANGEAGENTID uniqueidentifier = null
                )
                with execute as caller
    as
    begin
      set nocount on;

                declare @CURRENTDATE datetime
                declare @EVENTID uniqueidentifier
                declare @BASECURRENCYID uniqueidentifier
                declare @BENEFITOPTIONID uniqueidentifier
                declare @BENEFITPREFERENCE nvarchar(500)

                declare @BENEFITOPTIONS table (ID int identity(1,1), BENEFITOPTIONID uniqueidentifier);

                declare @BENEFITPREFERENCES table (ID int identity(1,1), BENEFITPREFERENCE nvarchar(500));

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @i int
                declare @TOTAL int


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

                set @CURRENTDATE = getdate();

                SELECT @EVENTID = EVENTID from dbo.EVENTSPONSOR where ID = @SPONSORID;

                SELECT @BASECURRENCYID = BASECURRENCYID from dbo.EVENT where ID = @EVENTID

                if ISNULL(@BENEFITOPTIONIDLIST,'') > ''
                INSERT INTO @BENEFITOPTIONS(BENEFITOPTIONID)
                SELECT element_value from dbo.UFN_PARSE_STRING(@BENEFITOPTIONIDLIST,',');

                if ISNULL(@PREFERENCELIST,'') > ''
                INSERT INTO @BENEFITPREFERENCES(BENEFITPREFERENCE)
                SELECT element_value from dbo.UFN_PARSE_STRING(@PREFERENCELIST,',');

                SELECT @TOTAL = COUNT(*) from @BENEFITOPTIONS;
                SET @i = 1;

                BEGIN TRAN
                begin try                            

                    WHILE (@i <= @TOTAL)
                    BEGIN

                            SET @BENEFITOPTIONID = null;
                            SET @BENEFITPREFERENCE = '';

                            SELECT @BENEFITOPTIONID = BENEFITOPTIONID FROM @BENEFITOPTIONS WHERE ID = @i;

                            SELECT @BENEFITPREFERENCE = BENEFITPREFERENCE FROM @BENEFITPREFERENCES WHERE ID = @i;

                            IF @BENEFITOPTIONID IS NOT NULL
                            EXEC dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORBENEFITOPTION @ID = null, @CHANGEAGENTID = @CHANGEAGENTID, @SPONSORID = @SPONSORID, @BENEFITID = @BENEFITID, @BENEFITOPTIONID = @BENEFITOPTIONID, @BENEFITPREFERENCE = @BENEFITPREFERENCE, @BENEFITTYPECODE = @BENEFITTYPECODE

                            SET @i = @i + 1;


                    END

                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR
                    ROLLBACK TRAN
                    return 1;
                end catch

                COMMIT TRAN;
                return 0;            
      end