Multi-select Parameter - Single Quote Problem

Hi,
I am using SQL Server 2005 Reporting Services. I am trying to pass a
multi-select string value to my query but having difficulties with
single quotes. My query is:
SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
(@.CustomerType)
@.CustomerType is a multi-select parameter in my report. The value of
this parameter should be passed as 'Commercial','Residential' BUT I
think it is being passed as 'Commercial, Residential'
Please help.
Thanks
SajjadAre you putting in the single quotes in your selection? You shouldn't.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sajjad.mussani@.gmail.com> wrote in message
news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> Hi,
> I am using SQL Server 2005 Reporting Services. I am trying to pass a
> multi-select string value to my query but having difficulties with
> single quotes. My query is:
> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
> @.CustomerType is a multi-select parameter in my report. The value of
> this parameter should be passed as 'Commercial','Residential' BUT I
> think it is being passed as 'Commercial, Residential'
> Please help.
> Thanks
> Sajjad
>|||I am not putting single quote in my selection. My selection is done
using a Multi-select drop down box. What I was trying to say is that,
I think the parameter should be passed with single quotes around each
selection, but it is not.
Sajjad
Bruce L-C [MVP] wrote:
> Are you putting in the single quotes in your selection? You shouldn't.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <sajjad.mussani@.gmail.com> wrote in message
> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> > Hi,
> >
> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
> > multi-select string value to my query but having difficulties with
> > single quotes. My query is:
> >
> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> > (@.CustomerType)
> >
> > @.CustomerType is a multi-select parameter in my report. The value of
> > this parameter should be passed as 'Commercial','Residential' BUT I
> > think it is being passed as 'Commercial, Residential'
> >
> > Please help.
> > Thanks
> > Sajjad
> >|||I don't think that is the issue. I do this all the time. Is your query in a
stored procedure?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sajjad" <sajjad.mussani@.gmail.com> wrote in message
news:1158158039.291968.300150@.m73g2000cwd.googlegroups.com...
>I am not putting single quote in my selection. My selection is done
> using a Multi-select drop down box. What I was trying to say is that,
> I think the parameter should be passed with single quotes around each
> selection, but it is not.
> Sajjad
> Bruce L-C [MVP] wrote:
>> Are you putting in the single quotes in your selection? You shouldn't.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <sajjad.mussani@.gmail.com> wrote in message
>> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
>> > multi-select string value to my query but having difficulties with
>> > single quotes. My query is:
>> >
>> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
>> > (@.CustomerType)
>> >
>> > @.CustomerType is a multi-select parameter in my report. The value of
>> > this parameter should be passed as 'Commercial','Residential' BUT I
>> > think it is being passed as 'Commercial, Residential'
>> >
>> > Please help.
>> > Thanks
>> > Sajjad
>> >
>|||> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
From SQL perspective, this won't work. You cannot pass such parameter. You
must either use dynamic sql ('... cust in ''' + @.cust ...) or parse this
multiparameter into table variable and use this table in subsequent query.
Radim Hampel