lapucca
asked on
How can I pass 3 #temp tables from a sp to SSRS to use to create 3 tables?
Hi, I'm using sql 2005. Can this be done? Can someone show me an example? Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The 3 temp tables are created in one stored procedure. The 3 tables are related in creating them so it won't make sense and cannot break them down to 3 separate sp. I currently have 3 select statment at the end of the sp and when creating reports it only detects 1. Where can I see example on using parameters to use these 3 temp tables in 1 sp? Thank.
In order to give you the best solution, can you explain why do you need to display the results of the temp table in your report?
ASKER
I'm doing a differences report. Table one shows entries that are in our production side but not in the remote side. The 2nd is the opsite of that, it shows what's in the remote but not in the production. The 3rd shows the entries that exist in both but there are discrepencies in a couple of the colums. Thaks.
ASKER
Oh, and I want to output 3 different tables in the report for these 3 temp tables. Thanks.
ASKER
Each table would have their own column header since they all have some common columns but definitely some different ones too. The 3rd one has a lot more columns than the previous 2. Thanks.
Per devlab2012 comments, the best solution would probably be to use dynamic sql to conditionally output the temp table based on the parameter you pass the stored procedure. See this page as an example
http://www.mssqltips.com/tip.asp?tip=1160
http://www.mssqltips.com/tip.asp?tip=1160
ASKER
Thanks everyone. Yeah, creating the tables to the database then delete them is not going to work. Users who run the report will not have these type of permissions. I guess I would just need to have 4 (yeah, increase now to 4) sp. In each of the sp, the 2 temp tables would have to be re-created which is horrible performance and very in-efficient. However, that is the limit of working with SSRS.
You can get different #temp tables from one sp using some parameters or create three different sp.