report uniqueidentifier type pass

My report failed to returm any data due to a uniqueidentifier field parameter communication with store procedure.

Select cols, ...
From mytable
Where  ( WorkID in (....))   --WorkID is uniqueidentifier data type

I have passed string parameter and int flag parameter to SP successfully.
For an uniqueidentifier field, I used the same way as a string parameter associated with a varchar type field does. But I failed.

What would be the right to pass the parameter coresponding to an uniqueidentifier field to SP?

Thanks.
LVL 1
minglelinchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
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

Open in new window

Thanks to the INNER JOIN you'll only be getting records for the values returned by the fSplitString function.
0
minglelinchAuthor Commented:
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.
0
ValentinoVBI ConsultantCommented:
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)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

minglelinchAuthor Commented:
I do not use CTE here at all. I attached fSplitString function. It seems MAXRECURSION is option for cte.
fsplitstr.txt
0
minglelinchAuthor Commented:
I changed the SP input parameter @myUndentifyer size from  nvarchar(Max) to nvarchar(1000), the error is gone.
0
ValentinoVBI ConsultantCommented:
Okay, but your string will get truncated at 1000 characters then, so your result might not be complete.

Your current function is using a recursive CTE.  Would you mind replacing your function with another one?  There's a very good one in following article: Delimited list as parameter: what are the options?

The function is located in chapter 4 and is called ParmsToListViaXML.  That's the one I usually use and it works with @Parameters varchar(max) too.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
minglelinchAuthor Commented:
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.
0
minglelinchAuthor Commented:
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.
0
ValentinoVBI ConsultantCommented:
"Value" is indeed the right field to use.

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

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

Open 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.
0
minglelinchAuthor Commented:
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.
0
minglelinchAuthor Commented:
Great solution.
My issue is caused by db data.
Thanks a lot for help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.