spAddUpdate_SiteSurveys
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PKID | int | INOUT | |
| @CurrentUsersID | int | IN | |
| @ClientSitesID | int | IN | |
| @SiteContentsID | int | IN | |
| @Title | nvarchar(256) | IN | |
| @TitleVisible | bit | IN | |
| @SubTitle | nvarchar(1024) | IN | |
| @SubTitleVisible | bit | IN | |
| @State | int | IN | |
| @SubmitButtonText | nvarchar(40) | IN | |
| @NextButtonText | nvarchar(40) | IN | |
| @PrevButtonText | nvarchar(40) | IN | |
| @PrevButtonVisible | bit | IN | |
| @ExitButtonText | nvarchar(40) | IN | |
| @ExitButtonVisible | bit | IN | |
| @AllowMultipleSubmit | bit | IN | |
| @AllowViewResults | bit | IN | |
| @OnSubmitMsg | nvarchar(1024) | IN | |
| @ClosedMsg | nvarchar(1024) | IN | |
| @QuestionsXml | ntext | IN | |
| @AnswersXml | ntext | IN | |
| @ColumnsXml | ntext | IN | |
| @ColumnDataXml | ntext | IN |
Definition
Copy
CREATE procedure [dbo].spAddUpdate_SiteSurveys (
@PKID int OUTPUT,
@CurrentUsersID int,
@ClientSitesID int,
@SiteContentsID int,
@Title nvarchar(256),
@TitleVisible bit,
@SubTitle nvarchar(1024),
@SubTitleVisible bit,
@State integer,
@SubmitButtonText nvarchar(40),
@NextButtonText nvarchar(40),
@PrevButtonText nvarchar(40),
@PrevButtonVisible bit,
@ExitButtonText nvarchar(40),
@ExitButtonVisible bit,
@AllowMultipleSubmit bit,
@AllowViewResults bit,
@OnSubmitMsg nvarchar(1024),
@ClosedMsg nvarchar(1024),
@QuestionsXml ntext,
@AnswersXml ntext,
@ColumnsXml ntext,
@ColumnDataXml ntext
)
as
begin
set nocount on
begin tran T1
declare @sql_error int
declare @hasResponses bit
if (@PKID<=0)
begin
insert into SiteSurveys(ID, ClientSitesID, Title, TitleVisible, SubTitle, SubTitleVisible, State,
SubmitButtonText, NextButtonText, PrevButtonText, PrevButtonVisible, ExitButtonText, ExitButtonVisible,
AllowMultipleSubmit, AllowViewResults, OnSubmitMsg, ClosedMsg)
VALUES (@SiteContentsID, @ClientSitesID, @Title, @TitleVisible, @SubTitle, @SubTitleVisible, @State,
@SubmitButtonText, @NextButtonText, @PrevButtonText, @PrevButtonVisible, @ExitButtonText, @ExitButtonVisible,
@AllowMultipleSubmit, @AllowViewResults, @OnSubmitMsg, @ClosedMsg)
select @sql_error = @@error
select @PKID = @SiteContentsID
end
else begin
update SiteSurveys
set Title = @Title, TitleVisible = @TitleVisible, SubTitle = @SubTitle, SubTitleVisible = @SubTitleVisible,
State = @State, SubmitButtonText = @SubmitButtonText, NextButtonText = @NextButtonText,
PrevButtonText = @PrevButtonText, PrevButtonVisible = @PrevButtonVisible, ExitButtonText = @ExitButtonText,
ExitButtonVisible = @ExitButtonVisible, AllowMultipleSubmit = @AllowMultipleSubmit,
AllowViewResults = @AllowViewResults, OnSubmitMsg = @OnSubmitMsg, ClosedMsg = @ClosedMsg
where ID = @PKID
select @sql_error = @@error
end
if exists (select * from SiteSurveyResponses where SiteSurveysId = @PKID)
set @hasResponses = 1
else
set @hasResponses = 0
if (@sql_error = 0)
begin
if (@hasResponses = 1)
commit tran T1
else
begin
delete from SiteSurveyQuestions where SiteSurveysId = @PKID
-- cascade delete constraint on SiteSurveyAnswers will take care
-- of deleting the corresponding answers for the questions above
if @@error <> 0 rollback tran T1
declare @iDoc int
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
exec sp_xml_preparedocument @idoc output, @QuestionsXml
insert into @questions(OriginalId, SiteSurveysId, SiteSurveyQuestionTypesId, Sequence, Page, LinkText,
Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr)
select ID, @PKID, SiteSurveyQuestionTypesId, Sequence, Page, LinkText,
Question, SubTitle, RandomizeAnswers, Mandatory, MaxAnswerLen, ConstantSumValue, SkipAutoNbr
from OPENXML(@idoc, '/Survey/Question', 1)
with (ID 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)
exec sp_xml_removedocument @idoc
-- decode the question
update @questions set Question = dbo.fnXmlDecode(Question)
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
exec sp_xml_preparedocument @idoc output, @AnswersXml
insert into @answers(SiteSurveyQuestionsId, Sequence, Answer, NextQuestionId)
select SiteSurveyQuestionsId, Sequence, Answer, NextQuestionId
from openxml (@idoc, '/Survey/Answer', 1)
with (SiteSurveyQuestionsId int, Sequence int, Answer nvarchar(1540), NextQuestionId int)
exec sp_xml_removedocument @idoc
-- decode the answer
update @answers set Answer = dbo.fnXmlDecode(Answer)
update @answers set SiteSurveyQuestionsId = a.ID
from @questions a
where a.OriginalId = SiteSurveyQuestionsId
update @answers set NextQuestionId = null
where NextQuestionId < 1
-- survey Answer Columns
exec sp_xml_preparedocument @idoc output, @ColumnsXml
insert into @columns(OriginalId, SiteSurveyQuestionsId, Sequence, ColumnName)
select ID, SiteSurveyQuestionsId, Sequence, Answer
from openxml (@idoc, '/Survey/Answer', 1)
with (ID int, SiteSurveyQuestionsId int, Sequence int, Answer nvarchar(256))
exec sp_xml_removedocument @idoc
-- decode the answer column
update @columns set ColumnName = dbo.fnXmlDecode(ColumnName)
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))
exec sp_xml_preparedocument @idoc output, @ColumnDataXml
insert into @columnData(SiteSurveyAnswerColumnsId, Sequence, Data)
select SiteSurveyAnswerColumnsId, Sequence, Data
from openxml (@idoc, '/Survey/AnswerColData', 1)
with (SiteSurveyAnswerColumnsId int, Sequence int, Data nvarchar(256))
exec sp_xml_removedocument @idoc
-- decode Column Data
update @columndata set Data = dbo.fnXmlDecode(Data)
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
commit tran T1
else
rollback tran T1
end
else
rollback tran T1
end
else
rollback tran T1
end
else
rollback tran T1
end
end
else begin
rollback tran T1
end
end