x

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

seagreenTurtle gravatar image

seagreenTurtle
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

RickD gravatar image

RickD
1.7k 1 1 4

There actually is such a thing as a temporary stored procedure. Create a stored procedure with a # at the begining of the name. The stored procedure will be dropped when the session closes and isn't available to any other sessions.
Nov 30, 2009 at 04:22 AM mrdenny
(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

mrdenny gravatar image

mrdenny
928 3

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x559
x415

asked: Nov 04, 2009 at 07:24 PM

Seen: 4650 times

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