create procedure [dbo].[MssWebGetOrderLocationBuildingConstraintQuestions]
    @inOrderId int,
    @inLocationId int
as
begin    
    set nocount on

    ;with LocationAndQuestions as
    (
        select
                OrderLocations.OrderLocationID,
                BuildingConstraintItems.BuildingConstraintItemID,
                BuildingConstraintItems.[Description],
                BuildingConstraintItems.Note,
                BuildingConstraintItems.YesNoFlag,
                BuildingConstraintItems.SortOrder,
                OrderLocations.BuildingFID
        from OrderLocations
        cross join BuildingConstraintItems
        where OrderLocations.OrderFID = @inOrderId and OrderLocations.OrderLocationID = @inLocationId
    )
    select
        Id = OrderLocationBuildingConstraint.OrderLocationBuildingConstraintID,
        BuildingConstraintItemId = LocationAndQuestions.BuildingConstraintItemID,
        [Description] = LocationAndQuestions.[Description],
        Note = coalesce( OrderLocationBuildingConstraint.Note, BuildingConstraintItemMap.Note, LocationAndQuestions.Note ),
        IncludeFlag = LocationAndQuestions.YesNoFlag,
        Flag = coalesce( OrderLocationBuildingConstraint.ConstraintFlag, BuildingConstraintItemMap.ConstraintFlag, cast( 0 as bit ) )
    from LocationAndQuestions
    left outer join OrderLocationBuildingConstraint on
        LocationAndQuestions.BuildingConstraintItemID = OrderLocationBuildingConstraint.BuildingConstraintItemFID and
        LocationAndQuestions.OrderLocationID = OrderLocationBuildingConstraint.OrderLocationFID
    left outer join BuildingConstraintItemMap on
        LocationAndQuestions.BuildingFID = BuildingConstraintItemMap.BuildingFID and
        LocationAndQuestions.BuildingConstraintItemID = BuildingConstraintItemMap.BuildingConstraintItemFID
    order by LocationAndQuestions.OrderLocationID, LocationAndQuestions.SortOrder, LocationAndQuestions.[Description]

end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOrderLocationBuildingConstraintQuestions] TO [MssExec]
GO