Can Report service run a Stored Procedure?

Hi, there,

I have a report that requires to join a lot of tables.

May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.

Thanks

I would use functions to break down the complexity. Call these functions from the stored procedure.|||

You could do that I suppose, although I'm not sure this is the best way. What about just using the stored procedure as the datasource for the report and not bother with a temp table at all?

If you do decide you need a temp table, then from what you're describing is sounds like you want a temp table that will continue to exist after the stored procedure has finished running. I will caution you that if you create a regular table in the database that you intend to use as a temp table (i.e. always truncating and repopulated the table when you run your stored proc) then you could run into problems if you have two users run your report at the same time (i.e. one run is truncating the table at the same time the other run is trying to retrieve data from the table). We've handled this in the past by creating an additional column in to hold the @.@.spid for the connection. That was years ago and I don't know if that's a good practice or not with MSSQL2005. I also don't know if that would work well with RS's usage of shared db connections.

|||

Thank you! eksplorer,

Your input is very helpful. if two users running the report at same time it will be a problem.

I am using report builder, may I ask how can you make stored procedure as a datasource when you are building a report model.

Thanks

|||

I haven't worked with report models. They way you do it in a reporting services report is to do something like this for the dataset:

= "exec pr_getmydata '" & User!UserID & "', '" & Parameters!prParm1.Value & "', " & Parameters!prParm2.Value & "'"

-bruce