Solved

TSQL Use results of a Stored Procedure in UNION

Posted on 2014-10-24
6
172 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 AOG and SQL2014 AOG 76 58
Rebooting Witness SQL Server 2 23
SQL Server Sum Over Multiple Tables 20 30
Applying Roles in Common Scenarios 3 13
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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