Multiple temp tables in stored procedure as dataset for SSRS report?

Hello, I am a newbie at SSRS and stored procedures so these questions will probably be pretty basic:

1) After writing a query containing a number of select into statements that create temorary tables, I do a 6 way join on all the temp tables at the end to get a list of IDs that all tables have in common (inner joins). If I add syntax to make the entire statement a stored procedure, can I use this as a basis for a report using sql server reporting services (version 2005)? I would just try it but I do not have access rights to add stored procedures to the database (when the DBA gets back from vacation he may give this to me). 2) Is it possible to make this work using a temporary stored procedure (#temp_sproc)? I don't see how as how would we select it as a dataset for a report in SSRS (I am using visual studio), as it only exists in memory when it's running.

The first question is what I need an answer for. the second is just to see if there's a chance I can do it without the ability to write stored procedures to the database.

Thanks in advance for any help. I have searched quite a bit for an answer but I only see examples where one temp table is used in a stored procedure.

seagreen turtle

more ▼

asked Nov 04, 2009 at 07:24 PM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

1) It is possible, but all the tables will need to exist when you design the report initially, otherwise reporting services throws an error.

2) There is no such thing as a temporary stored procedure, but you can do it with using the SQL you would use in the stored procedure. You would need to change this afterwards, which means a direct edit to the report file.

more ▼

answered Nov 05, 2009 at 01:25 PM

avatar image

1.7k 2 3 6

(comments are locked)
10|1200 characters needed characters left

Yes you can have SSRS run a stored procedure instead of a single long query.

more ▼

answered Nov 30, 2009 at 04:23 AM

avatar image

938 2 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 04, 2009 at 07:24 PM

Seen: 5241 times

Last Updated: Nov 04, 2009 at 07:24 PM

Copyright 2018 Redgate Software. Privacy Policy