report uniqueidentifier type pass

As far as I can tell, guids treated as varchar in the SP should work fine.  Beware though that they are quite long so you could run into a string limit when dealing with a lot of values. Defining the SP parameter as varchar(max) should resolve that.  Watch out, your fSplitString function will need a varchar(max) parameter too.

Perhaps try rewriting your query like this:

Select cols, ...
From mytable t
INNER JOIN dbo.fSplitString(@myUndentifyer, ',') s ON s.s = t.WorkID
Where NULLIF(@myFlag, -1) IS NULL OR t.FieldFlag = @myFlag

Select allOpen in new window

Thanks to the INNER JOIN you'll only be getting records for the values returned by the fSplitString function.

Thanks for posting. It's a nice thought for the above code.
I got error:  Conversion failed when converting a character string to uniqueidentifier. I changed the join to
INNER JOIN dbo.fSplitString(@myUndentifyer, ',') s ON Convert(varchar(1000),s.s) = Convert(varchar(1000),t.WorkID)
then the error is gone, but no data is returned. All t.WorkIDs are within 50 in length.

The function fSplitString should be fine, as I have other string filters using fSplitString work fine.

Then I changed the SP input parameter @myUndentifyer as nvarchar(MAX), I got error:
"An error has occurred during report processing. Cannot read the next data row for the dataset 'dsname'. The statement terminated. The Maximum recursion 100 has been exhausted before statement completion."

So is it data issue or length issue, or other issues? I'm still looking for further solution. I appreciate nay help.

The Maximum recursion 100 has been exhausted before statement completion

Are you using a recursive CTE (Common Table Expression) somewhere?  Perhaps in the fSplitString function?  100 recursions is the default. You can use the MAXRECURSION query hint to change this, see example C here: Query Hints (Transact-SQL)

I do not use CTE here at all. I attached fSplitString function. It seems MAXRECURSION is option for cte.
fsplitstr.txt

I changed the SP input parameter @myUndentifyer size from  nvarchar(Max) to nvarchar(1000), the error is gone.

Nice article. Thanks for sharing.

I used your ParmsToListViaXML, but wondering the field name to get from SP.
I tried
INNER JOIN dbo.fSplitString(@myUndentifyer, ',') s ON s.s = t.WorkID
also tried
Where  (SSV.WorkID in (select  rtrim(ltrim(s))  from dbo.ParmsToListViaXML(@Param, ',')))
but s is complained as an invalid column name. I used result, val, value as column name, none of them works.

I used Value in stead of value. Compile is fine when I used one of the three as below:
INNER JOIN dbo.ParmsToListViaXML(@Param, ',') s ON s.Value = t.WorkID
WHERE (t.WorkID) in (select  rtrim(ltrim(Value))  from dbo.ParmsToListViaXML(@Param, ',')))
WHERE (Convert(varchar(555),t.WorkID) in (select  rtrim(ltrim(Value))  from dbo.ParmsToListViaXML(@Param, ',')))

But no data are returned when I run the report.
If I remove this filter in SP, report data are returned.

"Value" is indeed the right field to use.

To debug this, create a new SP with simple code like:

select * from dbo.ParmsToListViaXML(@Param, ',')

Select allOpen in new window

Set up a new dataset, connect it with your multi-GUID parameter, dump the output in a new table on the report and see what you get there.

I did the above steps, data from ParmsToListViaXML(@Param, ',') are showing in the new table. So ParmsToListViaXML works as expected.

But if I uncommon the join -
INNER JOIN dbo.ParmsToListViaXML(@Param, ',') s ON s.Value = t.WorkID
or uncommon
Where  t.WorkID in (select  rtrim(ltrim(Value))  from dbo.ParmsToListViaXML(@Param, ','))
the report data is gone.

Great solution.
My issue is caused by db data.
Thanks a lot for help.