I'm a big fan of Table-Valued Functions, as a way to centralize reusable logic in reports produced by SQL Server Reporting Services. I’ll refer to Table-Valued Functions as TVFs for the remainder of this article.
Full documentation for TVFs can be found here: MSDN
To support SSRS reporting, the way I currently tend to use TVFs is as follows:
Reasons I find the above process valuable:
- Centralize Logic. Currently we utilize a TVF as a centralized way to find an initial set of data, based on a parameter passed. Every reporting stored procedure joins to this centralized TVF so the dataset is retrieved the same way every time. In my case, it’s a population of students made available to the report. It goes without saying that standardizing this logic saves a lot of time if it has to change.
- Replacement for a Stored Procedure. For SSRS reports, we use stored procedures as the source for each dataset (in fact, the DBA doesn’t permit queries from within an SSRS dataset - this is enforced by allowing Execute permissions on the schema we need, but not Select). Because we want to centralize a portion of the logic that isn’t report-specific, a developer’s first instinct could be to call this centralized logic from another stored procedure. However, that won’t work because the SSRS report server can only process the first result set returned. Therefore, we utilize a TVF lieu of another stored procedure so this process works for SSRS reporting.
- Replacement for a View. For SSRS reports, we pass a staff ID as a parameter in order to narrow down the set of data returned (for example: the set of students a teacher is allowed to see). Because a view cannot accept parameters, using a TVF for the centralized part of our logic instead of a view is very useful.
- Allows Complex Logic. We often use Common Table Expressions (CTEs) to arrive at a set of data in a multi-step process. A TVF allows you to use the power of CTEs to arrive at the final Select statement.
- Ease of Use. Since you can join to a TVF as any other database object, its syntax is very easy to use.
Below is my simplified “cheat sheet” for the syntax.
Creation of the Table-Valued Function:
CREATE FUNCTION RPT.ufnMyFunctionName
INSERT INTO @TableVariableName
FROM DW.Table1 AS T1
INNER JOIN DW.Table2 AS T2
ON T1.Field1 = T2.Field1
WHERE T2.Field3 = @Variable1
Usage of the Table-Valued Function within a Stored Procedure:
CREATE PROCEDURE RPT.uspReportingProcName
FROM DW.Table3 AS T3
INNER JOIN RPT.ufnMyFunctionName(@Variable1) AS T4
ON T3.FieldA = T4.FieldA
INNER JOIN DW.Table5 AS T5
ON T3.FieldB = T5.FieldB