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


  • Suggest to use a regular table (just like staging) stored in user database. You can index the table as well as create stats. If the table schema is not changing, the better to use truncate instead of dropping and re-creating the whole table. – Kin Shah Nov 28 '14 at 5:35

If your data needs to be persistent over a failure & 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.