79391901

Date: 2025-01-27 19:09:54
Score: 0.5
Natty:
Report link

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.

Reasons:
  • Long answer (-1):
  • Has code block (-0.5):
  • User mentioned (1): @multiValueParam
  • Low reputation (1):
Posted by: ItsANoBrainer