Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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.  
0
lapucca
Asked:
lapucca
1 Solution
 
itcoupleCommented:
Hi

SSRS accepts only one Stored procedure result per dataset. (Microsoft was suggested to change that using microsoft connect website).

So your options are:
1) Create 3 Sps
2) Expand Sp to pass extra parameter which will identify which temp table you want to use.
3) If the structure of the temp tables is exactly the same you can add extra table (table1, table2 , table2) and union them and in SSRS in each dataset just filter based on the extra column.

Hope that helps
Emil
0
 
devlab2012Commented:
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.
0
 
lapuccaAuthor Commented:
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
BostonMACommented:
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?
0
 
lapuccaAuthor Commented:
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.
0
 
lapuccaAuthor Commented:
Oh, and I want to output 3 different tables in the report for these 3 temp tables. Thanks.
0
 
lapuccaAuthor Commented:
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.
0
 
BostonMACommented:
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


0
 
lapuccaAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now