spClone_Survey

            CREATE  procedure [dbo].[spClone_Survey]
            (
                @PKCloneID        int,
                @PKCloneFromID    int,
                @CurrentUsersID    int
            )
            as
            begin
                set nocount on
            begin tran T1

            declare @sql_error        int
                declare @newGuid uniqueidentifier

                insert into SiteSurveys(ID, ClientSitesID, Title, TitleVisible, SubTitle, SubTitleVisible, 
                LastActivityDate, SubmitButtonText, NextButtonText, PrevButtonText, PrevButtonVisible, 
                ExitButtonText, ExitButtonVisible, AllowMultipleSubmit, OnSubmitMsg, AllowViewResults, State, ClosedMsg)
                select @PKCloneID, ClientSitesID, Title, TitleVisible, SubTitle, SubTitleVisible, 
                NULL, SubmitButtonText, NextButtonText, PrevButtonText, PrevButtonVisible, 
                ExitButtonText, ExitButtonVisible, AllowMultipleSubmit, OnSubmitMsg, AllowViewResults, State, ClosedMsg    
                from SiteSurveys
                where ID = @PKCloneFromID

                select @sql_error = @@error

                if (@sql_error = 0) 
            begin
                declare @questions table (ID                         int,
                                            OriginalId                    int,
                                            SiteSurveysId                int,
                                            SiteSurveyQuestionTypesId    int,
                                            Sequence                    int,
                                            Page                        int,
                                            LinkText                     nvarchar(256),
                                            Question                     nvarchar(1540),
                                            SubTitle                     nvarchar(1540),
                                            RandomizeAnswers             bit,
                                            Mandatory                     bit,
                                            MaxAnswerLen                int,
                                            ConstantSumValue            int,
                                            SkipAutoNbr                    bit)

                -- Survey Questions
                insert into @questions(OriginalId, SiteSurveysId, SiteSurveyQuestionTypesId, Sequence, Page, LinkText, 
                Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr)
                select ID, @PKCloneID, SiteSurveyQuestionTypesId, Sequence, Page, LinkText, 
                Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr
                from SiteSurveyQuestions 
                where SiteSurveysId = @PKCloneFromID

                insert into SiteSurveyQuestions(SiteSurveysId, SiteSurveyQuestionTypesId, Sequence, Page, LinkText, 
                Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr)
                select SiteSurveysId, SiteSurveyQuestionTypesId, Sequence, Page, LinkText, 
                Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr
                from @questions

                if @@error = 0
                begin
                    update @questions
                    set ID = a.id
                    from SiteSurveyQuestions a
                    inner join @questions b
                    on b.Sequence = a.Sequence
                    and b.SiteSurveysId = a.SiteSurveysId
                    and b.SiteSurveyQuestionTypesId = a.SiteSurveyQuestionTypesId
                    and b.Page = a.Page

                    declare @answers table (SiteSurveyQuestionsId    int,
                                                Sequence                 int,
                                                Answer                     nvarchar(1540),
                                                NextQuestionId            int)

                    declare @columns table (Id                        int,
                                                OriginalId                int,
                                                SiteSurveyQuestionsId    int,
                                                Sequence                 int,
                                                ColumnName                nvarchar(256))

                    -- survey Answers
                    insert into @answers(SiteSurveyQuestionsId, Sequence, Answer, NextQuestionId)
                    select a.SiteSurveyQuestionsId, a.Sequence, a.Answer, a.NextQuestionId
                    from SiteSurveyAnswers a
                    inner join SiteSurveyQuestions b on a.SiteSurveyQuestionsId = b.ID
                    where b.SiteSurveysId = @PKCloneFromID

                    update @answers set SiteSurveyQuestionsId = a.ID
                    from @questions a
                    where a.OriginalId = SiteSurveyQuestionsId

                    update @answers set NextQuestionId = null
                    where NextQuestionId < 1

                    -- survey Answer Columns
                    insert into @columns(OriginalId, SiteSurveyQuestionsId, Sequence, ColumnName)
                    select a.ID, a.SiteSurveyQuestionsId, a.Sequence, a.ColumnName
                    from SiteSurveyAnswerColumns a
                    inner join SiteSurveyQuestions b on a.SiteSurveyQuestionsId = b.ID
                    where b.SiteSurveysId = @PKCloneFromID

                    update @columns set SiteSurveyQuestionsId = a.ID
                    from @questions a
                    where a.OriginalId = SiteSurveyQuestionsId

                    insert into SiteSurveyAnswers(SiteSurveyQuestionsId, Sequence, Answer, NextQuestionId)
                    select SiteSurveyQuestionsId, Sequence, Answer, NextQuestionId
                    from @answers

                    if @@error = 0
                    begin
                        insert into SiteSurveyAnswerColumns(SiteSurveyQuestionsId, Sequence, ColumnName)
                        select SiteSurveyQuestionsId, Sequence, ColumnName
                        from @columns

                        if @@error = 0
                        begin
                                update @columns
                                set ID = a.ID
                                from SiteSurveyAnswerColumns a
                                inner join @columns b
                                on a.SiteSurveyQuestionsId = b.SiteSurveyQuestionsId
                                and a.Sequence = b.Sequence

                                declare @columnData table (SiteSurveyAnswerColumnsId    int,
                                                            Sequence                     int,
                                                            Data                        nvarchar(256))

                                insert into @columnData(SiteSurveyAnswerColumnsId, Sequence, Data)
                                select a.SiteSurveyAnswerColumnsId, a.Sequence, a.Data
                                from SiteSurveyAnswerColumnData a
                                inner join SiteSurveyAnswerColumns b on a.SiteSurveyAnswerColumnsId = b.ID
                                inner join SiteSurveyQuestions c on b.SiteSurveyQuestionsId = c.Id
                                where c.SiteSurveysId = @PKCloneFromID

                                update @columnData set SiteSurveyAnswerColumnsId = a.ID
                                from @columns a
                                where a.OriginalId = SiteSurveyAnswerColumnsId

                                insert into SiteSurveyAnswerColumnData(SiteSurveyAnswerColumnsId, Sequence, Data)
                                select SiteSurveyAnswerColumnsId, Sequence, Data
                                from @columnData

                                if @@error = 0                    
                                begin
                                    commit tran T1
                                    set @newGuid = NEWID()
                                    exec spAuditThis @CurrentUsersID, 1, @NewGuid, 1
                                end    
                                else
                                    rollback tran T1
                        end
                        else
                                rollback tran T1
                    end
                    else
                        rollback tran T1
                end
                else
                    rollback tran T1
            end
            else begin
                rollback tran T1
            end
            end