Stored Procedures or Table-valued Functions for SSIS and SSRS?
If you have written stored procedures as data source for SSRS reports, you must have noticed some weird behavior in the dataset designer. But after a while, you would figure out the reason for the odd behavior. The dataset designer initially does not have the “Contract” of the column definitions of data returned from the procedure, or the metadata that SSIS needs to properly populate the column definition.
Once you understand the reason, you would start to figure out a way to “fool” SSRS.
1) When you first set a data set in SSRS to use a stored procedure, go ahead click OK on the data source designer. It will throw an error because it is unable to obtain column definitions. No need to be scared of the error message. Finish by going to the Parameter tab and set up your parameters there.
2) Close the data set designer, and click the "Refresh fields" button on the tool bar. SSRS will prompt you for input parameters. Based on the results returned from the execution, SSRS will get the metadata needed and will be able to populate the column definitions.
Now you might be feeling more comfortable about writing stored procedures as data sources for SSRS reports. However, this is only one side of the story. Another side of the story is from within the stored procedure. You MUST take care in your procedure to return the SAME “contract” of columns under ALL possible scenarios. If you don’t, SSRS will throw error messages you might not understand.
Here is an example to show how you would take care in your procedure to cover all possible scenarios.
Here is simple template I would use. You can certainly write you the procedure with your own style. But the key issue is that you need to make sure that your stored procedure returns the same definitions of the columns from every possible branch. This is applicable when you have your return statement in IF statement.
As for SSIS, I use stored procedures in SQL execution tasks, not to return data set, but to process data and create milestone points and staging.
Many people have written in their blogs about calling stored procedures in data flow. In this case, the stored procedure is not creating physical staging, but return a data set to the data flow as a source. Similar problem about “contract” of column definitions happens here. Unfortunately SSIS isn’t easy to “fool” in this case. Without the metadata of the columns, the SQL Command Text editor will not allow you to more on.
Here is a blog, “31 Days of SSIS – No More Procedures (20/31)”, arguing that instead of using stored procedures, the best practice is to use table-valued functions in SSIS.
Here is it’s argument:
Whether the table-valued function is inline or multi-line, there is always a defined set of columns that will be returned. The SSIS data source knows specifically what will be returned from the function.
I mostly agree with this and will test it when I have time. However, table-valued functions are not procedures, and have it’s own limitations. Some of the limitations are well put in this blog, SQL SERVER – User Defined Functions (UDF) Limitations, such as
- No Access to Stored Procedure
- Prohibit Usage of Non-Deterministic Built-in Functions
- Returns Only One Result Set or Output Parameter
- an not Execute Dynamic SQL or Temporary Tables
- can not Return XML
- does not Support Error Handling.
Table-values functions have it’s place, such as perform repetitive task in SELECT statement or modularize frequently used code. It’s not really a tool for processing data with complex business rules.
I might still stick with stored procedures in the future. But it’s good to know all the options out there.