USP_DATAFORMTEMPLATE_EDIT_MARKETINGSURVEYQUESTION

The save procedure used by the edit dataform template "Marketing Survey Question Edit Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(50)	IN	Name
@QUESTIONTEXT	nvarchar(75)	IN	Question
@ISACTIVE	bit	IN	Mark question active
@ANSWERS	xml	IN	Answers
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MARKETINGSURVEYQUESTION (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @NAME nvarchar(50),
                    @QUESTIONTEXT nvarchar(75),
                    @ISACTIVE bit,
                    @ANSWERS xml
                )
                as

                    set nocount on;

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

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    begin try
                        declare @MARKETINGSURVEYQUESTIONID uniqueidentifier
                        select @MARKETINGSURVEYQUESTIONID = MARKETINGSURVEYQUESTIONID
                        from dbo.SALESMETHODMARKETINGSURVEYCONFIGURATION
                        where ID = @ID

                        -- update question properties

                        update dbo.MARKETINGSURVEYQUESTION set
                            NAME = @NAME,
                            QUESTIONTEXT = @QUESTIONTEXT,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @MARKETINGSURVEYQUESTIONID

                        declare @ANSWERTABLE table(ANSWERID uniqueidentifier, ISACTIVE bit, ANSWERTEXT nvarchar(100))
                        insert into @ANSWERTABLE
                        select 
                            T.items.value('(ANSWERID)[1]','uniqueidentifier'),
                            T.items.value('(ISACTIVE)[1]','bit'),
                            T.items.value('(ANSWERTEXT)[1]','nvarchar(50)')
                        from @ANSWERS.nodes('/ANSWERS/ITEM') T(items)

                        -- remove deleted answers

                        delete from dbo.MARKETINGSURVEYANSWER
                        where MARKETINGSURVEYQUESTIONID = @MARKETINGSURVEYQUESTIONID
                        and not exists(select 1 from @ANSWERTABLE where ANSWERID = MARKETINGSURVEYANSWER.ID)

                        -- add the new answers

                        insert into dbo.MARKETINGSURVEYANSWER
                            (ID, MARKETINGSURVEYQUESTIONID, ANSWERTEXT, ISACTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        select
                            newid(),
                            @MARKETINGSURVEYQUESTIONID,
                            AT.ANSWERTEXT,
                            AT.ISACTIVE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from @ANSWERTABLE AT
                        where AT.ANSWERID is null

                        -- update existing answers

                        update dbo.MARKETINGSURVEYANSWER set
                            ISACTIVE = AT.ISACTIVE,
                            ANSWERTEXT = AT.ANSWERTEXT,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        from @ANSWERTABLE AT
                        where AT.ANSWERID = MARKETINGSURVEYANSWER.ID
                        and MARKETINGSURVEYQUESTIONID = @MARKETINGSURVEYQUESTIONID

                        -- update the sales method marketing survey config record

                        update dbo.SALESMETHODMARKETINGSURVEYCONFIGURATION set
                            ISACTIVE = @ISACTIVE
                        where ID = @ID

                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                return 0;