Search code examples
sqlsql-serverreporting-servicesnestedprocedures

SSRS - multiple stored procedures to be called based on a parameter

I have this specific issue in Microsoft SSRS 2008:

I have to execute a stored procedure, which will return data with the same columns, but in different formats:

EXEC Main_SP
   @View = .....

IF @View = Yearly,
BEGIN
  EXEC SP_Yearly_Data
END

IF @View = Quarterly,
BEGIN
  EXEC SP_Quarterly_Data
END

IF @View = Monthly,
BEGIN
  EXEC SP_Monthly_Data
END

IF @View = Weekly,
BEGIN
  EXEC SP_Weekly_Data
END

All the 4 procedures will have the same data structure, ie. the same columns, only the groupings will be different, and hence the number of rows will also differ.

Will this work successfully in SSRS ?

Is there a better way to do it?

And will the dataset in the SSRS Report Designer quickly refresh to provide me the data related to the @View parameter provided ?

Any suggestions will be greatly appreciated.

Please note that each of the 4 inner procedures have some 3-4 parameters, all identical.

Solution

  • You can use IF statements in your SQL query in SSRS reports. I don't see why your SQL wouldn't work.

    However, I think it would be cleaner to create a master stored procedure that also takes the @View parameter in addition to the others and it would do the conditional branching and return the results. This would also allow you to run it as a stored procedure in your SSRS dataset instead of a SQL statement with a bunch of IF conditions in it.

    When you change the parameter value you'll need to rerun the report but it will return the correct dataset instead of using the cached one because the value of the parameter will have changed.