create procedure [dbo].[MssWebInsertUpdateOrderLocationBuildingConstraint]
    @inLocationId int,
    @inCrossStreet varchar(1024),
    @inServiceEntrance varchar(1024),
    @inSiteNote varchar(1024),
    @inQuestions MssWebBuildingConstraintItemInsertions readonly
as
begin
    set nocount on;
    --update order location fields
    update OrderLocations
        set CrossStreet = @inCrossStreet,
        ServiceEntrance = @inServiceEntrance,
        SiteNote = @inSiteNote
    where OrderLocationID = @inLocationId;

    --insert,update or delete building constraints
    ;with BuildingConstraints as
    (
        select
            Id = questions.Id,
            BuildingConstraintItemId = questions.BuildingConstraintItemId,
            Flag = questions.Flag,
            Note = questions.Note,
            defaultNote = BuildingConstraintItems.Note,
            defaultFlag = BuildingConstraintItems.YesNoFlag
        from @inQuestions questions
        inner join BuildingConstraintItems on BuildingConstraintItems.BuildingConstraintItemID = questions.BuildingConstraintItemId
    )
    merge OrderLocationBuildingConstraint using BuildingConstraints on
    orderlocationbuildingconstraintId = BuildingConstraints.Id and
    BuildingConstraintItemFID = BuildingConstraints.BuildingConstraintItemId
    when not matched then insert
    (
        OrderLocationFID,
        BuildingConstraintItemFID,
        ConstraintFlag,
        Note
    )
    values(
        @inLocationId,
        BuildingConstraints.BuildingConstraintItemId,
        BuildingConstraints.Flag,
        BuildingConstraints.Note)
    when matched and ltrim(rtrim(isnull(BuildingConstraints.note,''))) = ltrim(rtrim(isnull(BuildingConstraints.defaultNote,''))) and BuildingConstraints.Flag = 0
        then delete
    when matched then
        update set
        note = BuildingConstraints.note,
        ConstraintFlag = BuildingConstraints.flag;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebInsertUpdateOrderLocationBuildingConstraint] TO [MssExec]
GO