Here are my solutions without needing any any extra parameters.
If you only need to know if there exists values or not, you can adjust your conditional based on your needs.
-- Returns total number of characters.
SELECT LEN(CONCAT(@multiValueParam,''));
If you need to get each value separately:
-- Returns multiple rows, one for each element in the parameter.
SELECT *
FROM STRING_SPLIT( SUBSTRING(CONCAT_WS(',',@multiValueParam,''), 1, LEN(CONCAT_WS(',',@multiValueParam,''))-1) ,',');
If you need to get just the number of elements:
-- Returns total number of elements in parameter.
SELECT COUNT(*)
FROM STRING_SPLIT( SUBSTRING(CONCAT_WS(',',@multiValueParam,''), 1, LEN(CONCAT_WS(',',@multiValueParam,''))-1) ,',');
MSSQL will get angry about not enough parameters in the functions, so we need to use a dummy value of empty string to get around it.
We use CONCAT_WS
to turn our multi-values into a single string. This causes our concats with separators to have an extra separator at the end, which splits into an extra multi-value.
We use SUBSTRING
to remove this extra comma at the end of our CONCAT_WS
string.
We use STRING_SPLIT
with our separator to pull our the individual values.
You can test by replacing @multiValueParam with 'test1','test2'
exactly, which is basically what SSRS does when putting multi-value parameter into your query. You can also use any separator if you data happens to have commas.