TSQL Use results of a Stored Procedure in UNION

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?
MrDavidThornAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Store that recordset in temp table then select from temp table and use according from your needs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
MrDavidThornAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
MrDavidThornAuthor Commented:
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.