Solved

TSQL Use results of a Stored Procedure in UNION

Posted on 2014-10-24
6
167 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
  • 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 65

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

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 65

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

861 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now