…and T-SQL and beyond actually.
It seems that every few months a question pops up on a blog, forum, twitter or smoke signal about the behavior of multi-value parameters in SQL Server…or in a broader sense, how to pass multiple values to a stored procedure in T-SQL.
In T-SQL stored procedures, there really isn’t a native method to pass in arrays of data, but the building blocks are there. It’s really not expected, so it’s not fair to question that. But the thing that gets developers in a tizzy is what appears to be a bug, oversight or WTF moment in the SSRS environment since in a half-pregnant sort of way, it works OTB with hard-coded SQL SELECT statements.
Why Developers Go WTF? with SSRS Parameters
SQL Server Reporting Services parameters can be configured to operate as single value or multivalue. As MultiValue, it means that one, many or all selections can be made from a drop down selector on the UI. This is then passed to your dataset logic to return a set of values.
Consider Parameter @Color that is a is a list of distinct colors from the AdventureWorks product catalog. In your dataset, you might want to return all Products that match the selected color or batch of colors. Using the dataset creation UI, or writing it yourself you’d get the following:
select ProductID, Name from Production.Product p
where p.Color in (@Parameter)
When you do this, SSRS does the heavy lifting to convert the selection of colors from your paramater to something nicely formatted for the IN clause. There’s really no thought required on your part.
So now, you think to yourself, “Self, I really don’t want to retype SQL queries in my SSRS datasets…and I want some reusability.” You then think to yourself, “Self, why do I always call myself ‘self’ when thinking in third person? You’d think I’d be more creative…I mean, there’s nobody here but you and I…why not call myself something more creative, like Captain Caveman or hey…Spiderman? I still have that outfit for ‘special nights’. Oh yeah, the royalties involved, scratch that.” Anyway, you peel off the Underoos and you build a stored procedure that has the syntax above and try the following.
And guess what? No soup for you. Do not pass Go. Do not collect $200. Because the array isn’t passed the way you’d expect to your back end logic.
I feel your pain. And so do many others. There are elaborate workarounds for this issue. Several involve building array deconstructors in T-SQL (they also involve elaborate array contructors as well unfortunately), this one involves changing the values to XML ((Actually, you should read the article I linked over there. Mark has a good spirit, although I think many will be lost on the Mystery Gang references…I’m feeling old). At the end of the day, this method is really an array constructor/deconstructor too if you look at it that way. I’m not a fan of these methods. If we took the XML approach, that modifies the listing logic. That means the drop down selecter of Characters, Customers, Colors, etc could only be used in conjunction with this routine. That also means every drop down requires this bit of code modification. Next, the dataset that returns the data for the report can only be used with this type of function.
Wait a minute, did somebody say function? Oh yeah. I did. By using a special type of function, called a Table Value Function, we can work with MultiValue Parameters in SSRS and as an added bonus, use the logic for any stored procedure that needs to accept multiple values.
What is a Table Valued Function?
Introduced in SQL Server 2005, table value functions are UDFs that return a table, or array of data rather than a simple scalar value. For more information on their usage and syntax, check Books Online.
I’m a big fan of reusability. Or maybe I’m lazy. Or maybe reusability is the muse of laziness? Or maybe laziness is the root of all evil. Or maybe Pluto isn’t really a planet. I don’t know. Anyway, I like to ensure that a stored procedure (or any piece of logic, really) has a fair shot of being used by other (future) routines or requests. By settling on a generic approach to passing multiple values to a stored procedure, you account not only for the unique requirements of an SSRS workaround. What you add is the ability to call this stored procedure and functionality from other T-SQL or stored procedure logic at any time.
In addition, without the hardcoding of say, an XML node, the method is easily dropped in to new reports without any real heavy lifting.
Can we get to the workaround already?
Oh right. The solution. So let’s start with the T-SQL function. I originally found some code tidbit online years ago that was used as a poor man’s ETL…to import data from a comma delimited file. I then modified it for my needs. What you’ll need first is the code below:
CREATE FUNCTION [dbo].[fn_String_To_Table] ( @String VARCHAR(max), /* input string */ @Delimeter char(1), /* delimiter */ @TrimSpace bit ) /* kill whitespace? */ RETURNS @Table TABLE ( [Val] VARCHAR(4000) ) AS BEGIN DECLARE @Val VARCHAR(4000) WHILE LEN(@String) > 0 BEGIN SET @Val = LEFT(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1), LEN(@String))) SET @String = SUBSTRING(@String, ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0), LEN(@String)) + 1, LEN(@String)) IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val)) INSERT INTO @Table ( [Val] ) VALUES ( @Val ) END RETURN END
The inputs are a long string specified as an varchar(MAX), and your delimiter value which could be a comma (,) , semicolon (;), pipe (|), whatever. While the return value is a table with a single column, you’ll need to specify the data type for that column. A varchar(4000) should provide enough growth for whatever you throw in there. If you’d like to be more conservative in your memory management and know that you’ll never have field widths greater than char(10), then go ahead and specify that instead. Also, assuming you don’t mix data types in your result set you can take advantage of the implicit data conversions that SQL Server will do for you in T-SQL.; meaning, it’s alright to pass a delimited string of integers to this list. But more on that later.
Lastly, if you want to kill leading or trailing spaces, use the flag on the end which takes a 1 for True or 0 for False.
The usage of that function would be something like:
SELECT Val FROM [dbo].[fn_String_To_Table]( ‘Apples, Bananas, Carrots, Peas’, ‘,’,1)
As the result set would be:
Two points to note. This return function is generic, so you’ll always call on “Val” for a return field. That’s nice. Second…doesn’t it look and smell like a table there?
So how do we use it in SSRS? We’ll get there grasshopper. But before we use this in SSRS, it’s important to see how we’d use it in stardard T-SQL. Consider the example I mentioned earlier: a stored procedure returning a list of Products based upon Color for AdventureWorks.
CREATE Proc [dbo].[Get_Products_For_Color] (@MultiVal nvarchar(max))
select ProductID, Name from Production.Product p where p.Color in (SELECT Val from dbo.fn_String_To_Table(@MultiVal,',',1))
To use this stored procedure, we could try the following:
A few notes at this point. This is reusable, but if you want to get real snitpicky, you can add a delimiter input parameter to this stored procedure. I’ve decided that all inputs will use a comma delimiter, but go on with your bad self if you feel the need. As mentioned earlier, implicit datatype conversion will be applied by SQL Server, so you can conceivably examine these return values against say, and integer like a ProductKey field. If you aren’t a fan of that, go ahead and CAST() your return values to whatever datatype you need. Lastly, if you do some searching on the intertubes for versions of this process you will likely note that many simply do a JOIN on the resulting table. Perhaps you even thought of that yourself as you were reading this. Something like this, perhaps:
select ProductID, Name
from Production.Product p
INNER JOIN dbo.fn_String_To_Table(@MultiVal,’,',1)) t
ON p.Color = t.Val
I DO NOT RECOMMEND THIS APPROACH. HERE’S WHY:
The nature of this stored procedure is to take a list of values and I like reusability. But we don’t know if the array will be passed from SSRS, from another T-SQL Stored Procedure or an ad-hoc user select. In essence, we don’t know what type of business logic (if any) will be applied to the query.
So consider this…What happens when someone passes a duplicate value into that array? Something like:
‘ BLACK, BROWN, BLACK, WHITE’
Seems harmless enough, but as a join we’d now see a duplicate for every single product that is BLACK. As part of the IN condition of a WHERE clause we are immune to that problem. Unless of course, you want to see duplicates.
Ok, so we got this far and you know how to pass an array into a stored procedure. Get crackin’ with that T-SQL logic.
Integrate with SQL Reporting Services
Meanwhile back at the farm, Jimmy wanted to get a MultiValue parameter to a stored procedure.
- Create a Reporting Services project and add a new Report.
- Add a Datasource connecting to AdventureWorks.
- Create a Dataset for the Color list dropdown:
- Create a new Parameter called Color and attach this datasource. Set it to MulitValue, ‘natch.
- Define the values using the Colors dataset:
- Create a datasource for the report called Products, and use the Stored Procedure we created earlier:
- Click the Parameters tab (Note to self: “can we still call it a tab, now that tabs are extinct?” And whatever happened to tabs?…they seem so 1998).
- Note that @MultiVal has been added as a parameter name. Even though you are tempted, DO NOT SELECT THE PARAMETER @COLORS FROM THE DROP DOWN.
- Instead, click the function icon(fx) to fire up the Expression Builder.
- Format the SSRS Parameter array to a delimeted string with the following expression:
Note: JOIN takes an array and “joins” it together with a delimeter of your choice. The second parameter shows that I want to join the values together with a comma. Also note that if you grab the Parameter name from the UI below, SSRS notes that this is a MultiValue parameter and will attempt to “help” you by selecting a single value..specifically Parameters!Color(0).Value . Which means, get the first selected value in the list. Parameters!Color(1).Value would specify the request for the second value in the list. Parameters!Color.Value requests all selected values, which is what we want.
- Save, then add a simple table on the Report layout.
- When you Preview, you’ll see something like this:
- Go ahead and select 1 value, 2 values or more.
About T-SQL Tuesday This post has been a contribution for T-SQL Tuesday (#tsql2sday on Twitter). This months theme Puzzling Situations, is hosted on Adam Machanic's blog. Ping those links for more tasty SQL snacks.