Hey, I am trying to write a stored procedure for a report body. I have one parameter @Status, which I need to be multivalued. What I am doing right now is, I am doing a Union between 3 sources something like this: Select col1,col2,col3....etc From S1 JOIN S2 JOIN S3 JOIN S4 (Just example) Where s4.Status in (SELECT VALUE FROM dbo.split(RTRIM(@Status), ',')) Union Select col1,col2,col3....etc From S2 JOIN S1 JOIN S3 JOIN S4 (Just example) Where s4.Status in (SELECT VALUE FROM dbo.split(RTRIM(@Status), ',')) Union Select col1,col2,col3....etc From S3 JOIN S4 JOIN S5 JOIN S6 (Just example) Where S6.Status = CASE WHEN f1 IS NOT NULL THEN 'Done' WHEN f2 IS NOT NULL THEN 'Processing' WHEN f3 IS NOT NULL THEN 'Declined' ELSE NULL END IN (SELECT VALUE FROM dbo.ufn_Split(RTRIM(@Status), ','))The 3rd source is the one giving me problems. Some how, i am not able to get that parameter to work with my split function.Let me know if I was not very clear. In the 1st 2 sources i can directly use my field with my split function. But using a case statement in my where clause with the split function is the problem.Any thoughts/ideas/suggestions?

Change to: Where s4.Status in (@Status)

I want it to be separated by commas, hence, I am using the split function. But, the main problem is with s6.status part ! That is the part in my 3rd union which is giving me an error.

Reading that, when f1 is not null, that clause would be the equivelent of S6.Status = 'Done' IN (SELECT VALUE FROM dbo.ufn_Split(RTRIM(@Status), ',')). As you can see that makes no sense having both the = and the IN comparators. Do you want to find S6.Status in the select results? or do you want to find S6.Status in the select results. What is the english desription of what you are trying to accomplish here?

What I want is, in my SSRS report in the dropdown list of my parameters, users should see the values associated to s4 and s6. So, for example if s4 has values like val1,val2,val3 and s6 has values as Done,Processing,Declined, so, when the user accesses the reports he should see val1,val2,val3,Done,Processing,Declined in the dropdown list of the parameter.

And if they select "Done" from the parameter the the report should show col1, col2, col3 data in a table or chart where S6.Status = Done?

Col 1, Col 2, Col 3 from the table where Status = "Done"

And if val1, val2 or val3 is selected then you won't check S6.Status but will check S4.Status instead? If that is the case then: WHERE @StatusParam = CASE WHEN @StatusParam IN (val1, val2, val3) THEN S4.Status ELSE S6.Status END IF