Solved

TSQL Use results of a Stored Procedure in UNION

Posted on 2014-10-24
6
171 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

839 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