Robb Melancon (5/10/2010)
Thanks for the reply. The only problem with using temps is that they would need to be global or declared directly in temp database like you said. This is fine for single user but when you have multiple users running reports I'd need to create a guid per each run of the report and filter on the guid. There will be other problem potentially with this like locking for inserts while some other user is running a report etc. It may be the only solution though. Another thing I thought would be passing the table as a parameter but this may have some performance issues. Anyway, thanks again for the reply.
In that case, perhaps you can create seperate temp tables for each users instead of filtering on a guid column, like:
tmp_5B12952D614E497A93F7EA670B279A75
tmp_5C0EDDEFE61740EDAB5A0DDBC67088F0
I think that won't require filtering on guid and won't have locking issues. Only need to optimize tempdb a bit or even creating a user database as the temp db.