Solved

TSQL Use results of a Stored Procedure in UNION

Posted on 2014-10-24
6
180 Views
Last Modified: 2014-10-24
Hi Experts

I'm not sure if this is possible, first of all I'm not a DBA on the SQL server and my access is restricted.

I have a stored procedure that returns a recordset. With SSRS I want to filter that recordset and join with some other data.

Is this possible?
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40402120
Store that recordset in temp table then select from temp table and use according from your needs
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40402140
>I have a stored procedure that returns a recordset.
>With SSRS I want to filter that recordset and join with some other data.

Yes, this is possible by editing a Data Set with custom T-SQL to do the SP call/join.
However, it's not considered best practice to embed a lot of T-SQL into an SSRS report, as it's easier to do impact analysis / change control if as much T-SQL as possible is in the database as opposed to in files all over the place.   Plus T-SQL on the server can be optimized, T-SQL in an SSIS and SSRS file cannot.

So ... I'd recommend creating another SP or table-valued function that accepts parameters, calls the SP, and returns the set.   Test to verify it works, execute that on SQL Server, and then in your SSRS report call that object.
0
 

Author Closing Comment

by:MrDavidThorn
ID: 40402161
Many thanks. I also agreed with Jims comments except that I have no write access to create any SP on the db. Mentioned that my access rights was restricted.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40402188
>I have no write access to create any SP on the db
>Store that recordset in temp table
Curiosity overwhelms me ... if you don't have rights to create a SP, explain how you would have rights to execute any code that accepts parameters that creates a temp table, and then how you would call that code on-demand.
0
 

Author Comment

by:MrDavidThorn
ID: 40402273
It's the SSRS account via report manager  that has access, so unfortunately have to go down the route of having TSQL scripts running through reports, bad practice I know!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40402302
I'd consider asking the DBA if they would create a SP that exports the  SP you're talking about into a temp table, then schedule it through SQL Agent at regular intervals.   At least that would avoid the step of having to extract the data and store it somewhere else.

It's also a good idea to identify the group in your company that does SQL Server development, and ask them if they could take this on as a project.

I still don't think the answer you accepted is a very good one, as if you don't have rights to create SP's then it's also likely that you don't have rights to create tables, at least on that database, which means the solution would be to create it on a local/unrestricted database, then do a cross-database JOIN.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question