Experienced business intelligence (BI) developers would tell you that as you move from one project to another, some requirements start becoming repetitive like you have dealt with them before. One such repetitive requirement occurs during SQL Server Reporting Services (SSRS) development wherein a client would request that a report parameter be configured to allow multiple values from a dataset that is populated by stored procedure, as illustrated in Figure 1.
Figure 1
In the backend, the selected multiple parameter values are passed onto the report stored procedure as one long delimited string as shown in Script 1.
|
1 |
EXEC [dbo].[rpt_spDemo] 'Chelsea,Arsenal |
Script 1
It then becomes the responsibility of the report stored procedure to be able to break down the long delimited-string into multiple rows so they can be evaluated in a WHERE clause of the stored procedure query. One way of breaking down the delimited parameter values is by using a user-defined table-valued function. Script 2 shows my well-trusted table-valued function code that I usually utilize for splitting values into multiple rows – the function assumes that the string to be split uses a comma delimiter but this can always be modified according to your own delimiter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION [dbo].[func_CommaDelimitedString] (@val nvarchar(100)) RETURNS @clublist TABLE (clubname nvarchar(55)) BEGIN DECLARE @x int = 1 DECLARE @y int = CHARINDEX(',', @val) WHILE @x < LEN(@val) + 1 BEGIN IF @y = 0 BEGIN SET @y = LEN(@val) + 1 END INSERT INTO @clublist (clubname) VALUES (SUBSTRING(@val, @x, @y - @x)) SET @x = @y + 1 SET @y = CHARINDEX(',', @val, @x) END RETURN END |
Script 2
Having copy-pasted the code in Script 2 and successfully created the function, all that is left to do is simply alter the report stored procedure and modify the WHERE clause to reference the newly created table-valued function as indicated in Script 3.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROC [dbo].[rpt_spDemo] @ClubName [varchar](985) AS SELECT [Position], [Club], [Goals] FROM [dbo].[view_RPT_Demo] WHERE [Club] IN ( SELECT clubname FROM [dbo].[func_CommaDelimitedString](@ClubName)) |
Script 3
One major benefit of reusing existing scripts when dealing with a repetitive requirement is that you cut down on development time thus enabling you to focus on other aspects of the project. Yet, not all SQL Server environments will support the successful execution of your well-trusted scripts. I was recently involved in a project whose requirement felt like something I have done before in that the client – you guessed it – wanted one of the SSRS reports to be able to pass multiple parameter values into a stored procedure-based dataset. However, the stored procedure was stored in a SQL Server Parallel Data Warehouse (PDW) environment. Undoubtedly, SQL Server PDW as a data store has several benefits for business intelligence and related analytical projects. Yet, SQL Server PDW lacks support for basic features that are largely supported in a traditional SQL Server symmetric multiprocessing instance. This then makes it difficult to “plug and play” your well trusted script into a SQL Server PDW environment. In this article, I will cover some of the limitations of SQL Server PDW when it comes to splitting multi-parameter values passed from SSRS into multiple rows and later, I will provide you with a piece of string-splitter code that can successfully be executed in a SQL Server PDW to get your SSRS report working.
SQL Server PDW Limitation #1: Table-Valued Functions
You will soon realize that your so-called well-trusted table-valued function scripts are not very reliable because as soon as you execute them in a SQL Server PDW environment, you immediately run into the error message shown in Figure 2.
Figure 2
To be fair, the error returned in Figure 2 has more to do with the fact that return type TABLE in user defined functions is not recognized in SQL Server PDW. SQL Server PDW only supports the implementation of scalar-valued functions as demonstrated in Figure 3.
Figure 3
Since the table-valued function is not a viable option when it comes to SQL Server PDW, we need to find another mechanism for splitting delimited string values passed from an SSRS dataset.
SQL Server PDW Limitation #2: Recursive Common Table Expressions (CTE)
Recursive common table expressions (CTE) provides us with another mechanism for splitting delimited string into multiple rows. Figure 4 shows a sample recursive CTE script that references itself as part of breaking down a given comma delimited string into multiple rows. Although this recursive CTE script successfully executes in a traditional SQL Server instance, it fails to commit when run within a SQL Server PDW environment.
Figure 4
Similarly, to the behavior of user-defined functions, SQL Server PDW still supports the implementation of normal CTEs just not the recursive kind. Yet again, we still find ourselves with no viable option for splitting multi-valued parameter strings in SQL Server PDW. Thus, we continue to look for another method so we can get our SSRS report working.
SQL Server PDW Limitation #3: XML Nodes() Method
Another way we can split a delimited string into multiple rows would be to convert the delimited string passed by an SSRS report dataset into an XML document and query it using the nodes() method as shown in Figure 5.
Figure 5
Yet again, a well-trusted script that successfully executes in a traditional SQL Server instance breaks when being run on a SQL Server PDW platform. Furthermore, unlike with user defined functions and CTEs which are partially supported, SQL Server PDW doesn’t support XML data type at all. This is also true for CLR data types. Luckily, there is one more option that works in both traditional SQL Server and SQL Server PDW.
Solution: Temporary Table in a WHILE Loop
It turns out SQL Server PDW does support both the creation of temporary tables as well as the execution of WHILE loop statements. A combination of these two features enables us to setup a stored procedure script that can split a delimited string received from an SSRS report into multiple rows used for filtering the base query. The complete script is shown in Script 4.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @String varchar(550) = 'Chelsea,Arsenal' IF OBJECT_ID('tempdb..#tmp_CommaDelimitedString') IS NOT NULL DROP TABLE #tmp_CommaDelimitedString CREATE TABLE #tmp_CommaDelimitedString (val varchar(100)) DECLARE @IndexOfDelimeter int = CHARINDEX(',', @String),@y varchar(max),@x int = 1 IF @IndexOfDelimeter = 0 BEGIN INSERT INTO #tmp_CommaDelimitedString VALUES (@String) END SET @String = @String + ',' WHILE @IndexOfDelimeter > 0 BEGIN SET @y = SUBSTRING(@String, @x, @IndexOfDelimeter - @x) IF (@y <> '') INSERT INTO #tmp_CommaDelimitedString VALUES (@y) SET @x = @IndexOfDelimeter + 1 SET @IndexOfDelimeter = CHARINDEX(',', @String, @x) END SELECT [Position], [Club], [Goals] FROM [dbo].[view_RPT_Demo] WHERE [Club] IN (SELECT val FROM #tmp_CommaDelimitedString) |
Script 4
Conclusion
As SSRS report requirements go, providing a functionality to be able to pass multiple parameter values into a stored procedure-based dataset has to be one of the popular requirements from business. Yet, depending on your data store, implementing a string-splitter mechanism necessary for providing the required functionality can range from convenient to complicated. In this article, we have demonstrated that whilst it is possible to split delimited string using table-valued function, XML nodes method, recursive CTEs, in a traditional SQL Server instance only a combination of temporary tables and WHILE loop statements will successfully execute against a SQL PDW data store.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu