I have a form that has checkboxes for ' If Yes, check the areas where the sponsor will serve additional sites' and on 'Greater Phoenix area'. Both need to have a required attribute (*) beside each of them on the form. Currently they don't because of the sql script below sets them to not required. Here is image of the form:
I need assistance on how to write a SQL Script that will update the QuestionnaireGroupId's (19 and 20) below to be required. This part of the current script needs to be modified in a new script to update those to required. Also when the user checks 'Greater Phoenix Area' a message should show that says 'check all that apply' beside it. I believe that I need a MERGE statement in this new script but not sure?? Can someone please a solution to what I need to do to get create a updated script for this.
Original script
DECLARE @Required bit = 1, @NotRequired bit = 0,
@Rollover bit = 1, @NotRollover bit = 0
DECLARE @Textbox int = 1, @Checkbox int = 2, @Radio int = 3, @Datepicker int = 5
DECLARE @TempQuestions TABLE
(
QuestionnaireGroupId int,
ProgramGroupCode varchar(4),
ProgramGroupName varchar (20),
QuestionText varchar(1024),
ControlType int,
IsRequired bit,
IsRollover bit,
SortOrder int,
CreatedBy int,
CreatedDate datetime,
QuestionsAnswerOptionId int,
AnswerWeight int,
ControlParams varchar(1000) NULL
)
INSERT INTO @TempQuestions
(
SortOrder,
QuestionnaireGroupId,
ProgramGroupCode,
ProgramGroupName,
QuestionText,
ControlType,
IsRequired,
IsRollover,
CreatedBy,
CreatedDate,
QuestionsAnswerOptionId,
AnswerWeight,
ControlParams)
VALUES
.......
,(19, @GroupId, 'SFSP', 'SFSP', 'If Yes, check the areas where the sponsor will serve
additional sites.', 0, @Required, @NotRollover, @UserId, @UtcDate, 0, 1,
'{requiredChildren: 1}')
,(20, @GroupId, 'SFSP', 'SFSP', 'Greater Phoenix Area', @Checkbox, @NotRequired,
@NotRollover, @UserId, @UtcDate, 0, 1, '{showChildrenWhen: true, requiredChildren: 1}')
MERGE [SiteApp].[Questions] q
USING @TempQuestions temp
ON q.QuestionText = temp.QuestionText AND q.QuestionnaireGroupId =
temp.QuestionnaireGroupId AND q.SortOrder = temp.SortOrder
WHEN MATCHED
THEN UPDATE
SET
q.ProgramGroupCode = temp.ProgramGroupCode
, q.ProgramGroupName = temp.ProgramGroupName
, q.ControlType = temp.ControlType
, q.SortOrder = temp.SortOrder
, q.IsRequired = temp.IsRequired
, q.IsRollover = temp.IsRollover
, q.ModifiedBy = temp.CreatedBy
, q.ModifiedDate = temp.CreatedDate
, q.QuestionsAnswerOptionId = temp.QuestionsAnswerOptionId
, q.AnswerWeight = temp.AnswerWeight
, q.ControlParams = temp.ControlParams
WHEN NOT MATCHED BY TARGET
THEN INSERT (
SortOrder,
QuestionnaireGroupId,
ProgramGroupCode,
ProgramGroupName,
QuestionText,
ControlType,
IsRequired,
IsRollover,
CreatedBy,
CreatedDate,
QuestionsAnswerOptionId,
AnswerWeight,
ControlParams
) VALUES (
SortOrder,
QuestionnaireGroupId,
ProgramGroupCode,
ProgramGroupName,
QuestionText,
ControlType,
IsRequired,
IsRollover,
CreatedBy,
CreatedDate,
QuestionsAnswerOptionId,
AnswerWeight,
ControlParams
);
New script should update to show as required with message whenthe user checks 'Greater Phoenix Area' a message should show that says 'check all that apply' beside it. This is what I need help with is how do write a new script that updates the database to required for these two.
This is what I have so far below and what I need help with
DECLARE @CustomErrorMessage varchar(200) = ('Number of affected rows for Questions does not
match script.')
DECLARE @Required bit = 1, @NotRequired bit = 0,
@Rollover bit = 1, @NotRollover bit = 0
DECLARE @Textbox int = 1, @Checkbox int = 2, @Radio int = 3, @Datepicker int = 5
DECLARE @TempQuestions TABLE
(
QuestionnaireGroupId int,
ProgramGroupCode varchar(4),
ProgramGroupName varchar (20),
QuestionText varchar(1024),
ControlType int,
IsRequired bit
)
INSERT INTO @TempQuestions
(
SortOrder,
QuestionnaireGroupId,
ProgramGroupCode,
ProgramGroupName,
QuestionText,
ControlType,
IsRequired
)
VALUES
(19, @GroupId, 'SFSP', 'SFSP', 'If Yes, check the areas where the sponsor will serve
additional sites.', 0, @Required)
,(20, @GroupId, 'SFSP', 'SFSP', 'Greater Phoenix Area - Check all that apply below:',
@Checkbox, @NotRequired)
MERGE SiteApp.Questions q
USING @TempQuestions temp
ON q.IsRequired = temp.IsRequired
WHEN MATCHED
THEN UPDATE
SET q.ParentId = temp.ParentId;
IF @@ROWCOUNT <> (SELECT COUNT(QuestionText) FROM @TempQuestions)
THROW 52000, @CustomErrorMessage, 1;
If anyone has any suggestion on how to write this update script I would appreciate it.