EA7_spGetNewSurveyResponses

                CREATE  PROCEDURE [dbo].[EA7_spGetNewSurveyResponses]
                as
                begin
                    set nocount on

                    declare @surveys table (id         integer,
                                            Title     nvarchar(256) COLLATE database_default)
                    declare @surveyResponses table(
                        SiteSurveysId    integer,
                        ResponseId        integer,
                        EEId        integer,
                        DateAdded        datetime
                    )

                    -- list of Surveys answered by EE Users
                    insert into @surveys(id)
                    select distinct a.SiteSurveysId 
                    from dbo.SiteSurveyResponses a
                    inner join dbo.SiteSurveys c on a.SiteSurveysId = c.Id
                    where dbo.fnGetEEIDFromLinkedUserID(a.ClientUsersId) > 0
                    and a.EEProcessed = 0 

                    update @surveys
                    set Title = case when len(a.Title) > 0 then a.Title else c.title end
                    from dbo.SiteSurveys a
                    inner join @surveys b on a.id = b.id
                    inner join dbo.SiteContent c on a.id = c.id

                    -- result set 1 - Survey Responses delete transactions
                    -- convert the date to yyyy-mm-dd hh:mi:ss.mmm format that will work 
                    -- across US and UK databases
                    select s.SiteSurveysId 'id', s.ClientSitesId 'cid', convert(char(23), s.DeleteDate, 121) 'dt'
                    from dbo.SiteSurveyResponsesDeleteTransactions s
                    for XML AUTO

                    -- result set 2 - Surveys
                    select s.ID 'ID', s.Title 'Title'
                    from @surveys s
                    order by s.ID 
                    for XML AUTO

                    -- result set 3 - Survey Questions
                    select q.SiteSurveysId 'sid', q.ID 'qid', IsNull(q.Question, '') 'q', q.Sequence 'seq'
                    from dbo.SiteSurveyQuestions q
                    inner join @surveys b on q.SiteSurveysId = b.ID
                    order by q.SiteSurveysId, q.ID 
                    for XML AUTO

                    -- result set 4 - Survey Answers 
                    select a.SiteSurveyQuestionsId 'qid', a.ID 'aid', a.Answer 'a'
                    from dbo.SiteSurveyAnswers a
                    inner join dbo.SiteSurveyQuestions b on a.SiteSurveyQuestionsId = b.ID
                    inner join @surveys c on b.SiteSurveysId = c.id
                    order by a.SiteSurveyQuestionsId, a.ID
                    for XML AUTO

                    -- result set 5 - Survey Answer Columns
                    select c.SiteSurveyQuestionsId 'qid', c.ID 'cid', c.ColumnName 'c'
                    from dbo.SiteSurveyAnswerColumns c
                    inner join dbo.SiteSurveyQuestions b on c.SiteSurveyQuestionsId = b.ID
                    inner join @surveys d on b.SiteSurveysId = d.id
                    order by c.SiteSurveyQuestionsId, c.ID
                    for XML AUTO

                    insert into @surveyResponses (SiteSurveysId, ResponseId, EEId, DateAdded)
                    select a.SiteSurveysId, a.ID, dbo.fnGetEEIDFromLinkedUserID(a.ClientUsersId), a.DateAdded
                    from dbo.SiteSurveyResponses a
                    inner join @surveys b on a.SiteSurveysId = b.ID
                    where dbo.fnGetEEIDFromLinkedUserID(a.ClientUsersId) > 0
                    and a.EEProcessed = 0 

                    -- result set 6 - Survey Responses
                    select SiteSurveysId 'sid', ResponseId 'rpid', EEId 'eeid', convert(char(23), DateAdded, 121) 'dt'
                    from @surveyResponses rp
                    order by SiteSurveysId
                    for XML AUTO

                    -- result set 7 - Survey Results
                    select r.id 'id', r.SiteSurveyResponsesId 'rpid', r.SiteSurveyAnswersId 'aid', 
                    r.SiteSurveyAnswerColumnsId 'cid', 
                    case when (IsNumeric(r.AnswerValue)=1 and r.SiteSurveyAnswerColumnsId>0) then d.Data else r.AnswerValue end 'value'
                    from dbo.SiteSurveyResults r
                    inner join @surveyResponses b on r.SiteSurveyResponsesId = b.ResponseId
                    left outer join dbo.SiteSurveyAnswerColumnData d on Convert(nvarchar(50),d.Id) = r.AnswerValue
                    order by r.SiteSurveyResponsesId
                    for XML AUTO
                end