create procedure sys.sp_MSallocate_one_idrange
    @pub_range_begin numeric(38,0),
    @pub_range_end numeric(38,0),
    @pub_next_range_begin numeric(38,0),
    @pub_next_range_end numeric(38,0),
    @range bigint,
    @max_used numeric(38,0) output, -- input and output
    @range_begin numeric(38,0) output,
    @range_end numeric(38,0) output
as
    if @pub_range_end >= @max_used and  @max_used >= @pub_range_begin
    begin
        if (@max_used+@range) <= @pub_range_end
        begin
            -- there is enough space in the first range
            select @range_begin=@max_used
            select @range_end=@range_begin+@range
            select @max_used=@range_end
        end
        -- In a upgrade scenario the next range begin and end are null to begin with.
        else if @pub_next_range_begin is not null and @pub_next_range_end is not null
        begin
            -- we need to start using the second range
            select @range_begin = @pub_next_range_begin
            select @range_end=@range_begin+@range
            select @max_used=@range_end
        end
        else
        begin
            -- there is not enough range at the republisher to allocate for it subscriber
            raiserror(20665, 16, -1)
            return 1
        end
    end
    else if @pub_next_range_end >= @max_used and @max_used >= @pub_next_range_begin
    begin
        -- we are already in the second range
        if (@max_used+@range) <= @pub_next_range_end
        begin
            -- there is enough space in the second range
            select @range_begin=@max_used
            select @range_end=@range_begin+@range
            select @max_used=@range_end
        end
        else
        begin
            -- there is not enough range at the republisher to allocate for it subscriber
            raiserror(20665, 16, -1)
            return 1
        end
    end
	-- @max_used is lower than both the ranges (scenario in bug 536782)
    else if @pub_range_begin > @max_used
    begin
        -- allocate from the first range and set the new @max_used
        select @range_begin=@pub_range_begin
        select @range_end=@range_begin+@range
        select @max_used=@range_end
    end
    else
    begin
        -- there is something terribly wrong here. @max_used is not in the ranges available at the publisher
        raiserror(21197, 16, -1)
        return 1
    end

    return 0