Tempdb tables, table in database or even cte for usp for reporting I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data into a table that can be used In the ssrs report. 

So the question is for a result set of no more than a few hundred lines what sort of table should I use. One in the production dB. A temp table in tempdb or can you use cte ? 

The table will be dropped each run of the USP/ report

What comments are there

Thanks
If your data needs to be persistent over a failure &amp; restart then a "real" table in your database would be best.  You need a mechanism to isolate one user's execution of this from another's and to tidy up nicely.

If data is to be disposed of if the SP crashes and will be re-created on a restart then a table-valued variable would be adequate.  These are not indexed, however.  If the data will be re-read according to different predicates this may be a problem.

Temporary tables e.g. create table #abc... will keep one user's data isolated from another users'.  The tables will be tidied away at the end of the SP.  Indexes and statistics are held for #Tables so these perform better for larger row counts or multiple access paths.