SQLTeam.com | Weblogs | Forums

Need help writing SQL script that updates/merge from the original script

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.

Can anyone assist me with this please. I would appreciate anyone's input. Thanks

It's OK to reply to your own question with new information. It's not OK to bump your post.