0

I'm having a hard time trying to figure out how to make this work :

I have a big SQL query used for a report, that is run using "execute(@sql)". Withing @sql I have various select statements that I would like to filter using the values in the multi-value parameter passed from my report @filtervalues.

I've tested the following (this is an example query): 

Set @Sql='Select * from my table where field in ('+@filtervalues+')'

When I select a single value in the parameter, the profiler reads it as follows: 

Select * from mytable where field in(1234356-1234-1234-1234-123456)

So the selected Guid is without quotes, resulting in "error near 123456"

On the other hand, if I selected several values: 

Select * from mytable where field in ('+N'1234456-1231-1234-1244566',N'2345333-3243-2342-2342-23423423'+)

So it adds extra ' and +

Can you help me with this ?

Note: I cannot use the suggested solution on many websites to apply the filter on the table directly, because this would mess up the data.

Edit: The multi-value parameter is filled using this query dataset: 

select 'No Filter' as fullname,'00000000-0000-0000-0000-000000000000' as systemuserid
union all
select distinct su.fullname, convert(nvarchar(90),su.systemuserid)
from  FilteredSystemUser su
user3340627
  • 3,023
  • 6
  • 35
  • 80
  • 2
    Post the code on how you are assigning a value to `@filtervalues` as well please. – Dave C Jun 24 '14 at 14:14
  • @JiggsJedi I adjusted the question, is that what you mean ? – user3340627 Jun 24 '14 at 14:18
  • Close... Show us how you `SET @filtervalues =` or `SELECT @filtervalues =` please. – Dave C Jun 24 '14 at 14:19
  • Well, I don't do a "set @Filtervalues" in my query. I have a report parameter in which the user selects the required values. The @filtervalues is replaced by the report depending on the selected values. Please tell me if i'm missing something. – user3340627 Jun 24 '14 at 14:22
  • 1
    possible duplicate of [TSQL varchar string manipulation](http://stackoverflow.com/questions/712443/tsql-varchar-string-manipulation) – stubaker Jun 25 '14 at 21:42
  • SSRS replaces your multi-value parameter with a comma-separated string. To use those CSV's in dynamic SQL you will need to parse out the values and wrap them in single quotes (a couple ways to do that can be found at the question linked above) – stubaker Jun 25 '14 at 21:57
  • @stubaker thanks, I used a similar approach. – user3340627 Jun 26 '14 at 08:09

1 Answers1

0

I managed to fix this using the following :

1- In my Dataset, instead of passing the parameter as it is, I changed it to :

Join (Parameters!FilterValues.Value," ',' ")

to force the values to be sent as strings, the parameter value is as follows : 

1234567','122345','12345

2- In SQL, in my procedure, I added additional ' at the beginning and the end of the parameter to make up for the missing ones. So my code shows the following: 

Set @Sql='Select * from my table where field in ('''+@filtervalues+''')'
user3340627
  • 3,023
  • 6
  • 35
  • 80