Link to home
Start Free TrialLog in
Avatar of lapucca
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
Avatar of itcouple
itcouple
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
create three different datasets in SSRS for three different database tables.
You can get different #temp tables from one sp using some parameters or create three different sp.
Avatar of lapucca
lapucca

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?
Avatar of lapucca

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.
Avatar of lapucca

ASKER

Oh, and I want to output 3 different tables in the report for these 3 temp tables. Thanks.
Avatar of lapucca

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


Avatar of lapucca

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.