Hi

The following recursive option might work. It walks up and down from the queried invoice and joins the results.

DECLARE @invoiceID int = 3

;with rListUp as (

select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid

from #IDs a

CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b

where b.invoiceid > a.invoiceid

union all

select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid

from rListUp a

CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b

CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid > a.nextinvoiceid ) c

),

rListDown as (

select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid

from #IDs a

CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b

where b.invoiceid < a.invoiceid

union all

select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid

from rListDown a

CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b

CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid < a.nextinvoiceid ) c

),

rList AS (

SELECT invoiceid, nextinvoiceid FROM rListUp

UNION

SELECT invoiceid, nextinvoiceid FROM rListDown

)

select invoiceid, bookingid

from #IDs

where invoiceid in (

select nextinvoiceid from rlist where invoiceID = @InvoiceID

union

select @InvoiceID

)

It could probably be prettied up a bit more and there is still a chance that recursion limits will get hit if there is a long change of invoice/bookings