|
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
(comments are locked)
|
|
Yes you can have SSRS run a stored procedure instead of a single long query.
(comments are locked)
|
|
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. 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 '09 at 04:22 AM
mrdenny
(comments are locked)
|
