Thanks David,

never thought about this way..

But when i executed your procedure it is holding the lock for one table only..

I have tested this in Query analyzer removing the COMMIT TRANSACTION...

thanks for the procedure alzdba,

 But from where you are getting the ParameterValue

Thanks alzdba,

I learned one point that nested transactions don't lock each other..but i see people starting different transactions and committing in different places..

coming to my query i used the following statement...

sorry haven't given the current result

here you go

actual data

BookingId  start          finish

1          13/10/2004 09:00:00     13/10/2004 17:30:00   

2          13/10/2004 10:00:00     13/10/2004 12:30:00      

3          13/10/2004 17:30:00     13/10/2004 18:00:00   

4         ...

here is my data looks like

BookingId  start          finish

1          13/10/2004 09:00:00     13/10/2004 17:30:00   

2          13/10/2004 10:00:00     13/10/2004 12:30:00      

3          13/10/2004 17:30:00     13/10/2004 18:00:00   

4          08/10/2004 09:00:00     08/10/2004 18:00:00 ...

thanks David Burrows but this didn't work

and also i said earlier that i had solved my problem but this statement b1.Start <=b.Finish is also not giving...

i have found what causing this

here is what i have changed in my sp

b1.Start <=b.Finish

but still getting the error when i use dynamic order by clause in my...

thanks for responding steve

i know it is a bit long and hard to go through but that's the way the sp is and i couldn't figure out what was wrong with it.

i...

sridhar and john thanks for your response..

this has solved my problem..

Thank you for replying Morten,

 what i want is only the overlapped bookingid's (date ranges)..in the sample i have drawn only the first 4 should return as they are overlapped with...