Custom report multi-select stor proc problem

Former Member
Former Member $organization

Hi:

We have a new custom report (infomaker) that we want to be able to select multiple solicitors from a drop down in the report.  In the where clause of the stored procedure, I have a function to remove both single and double quotes from the passed variable (@solicitor), but I am getting a syntax error.  I get the same error when I use the charindex string (see eamples and errors below).

 

where 

 

dbo.lfn_matchparm(ISNULL(@solicitor,''), tsol.solicitor)>0) and tsol.campaign_no in (select campaign_no from t_campaign where category = 32 and fyear = @fyear)

Error:

Msg 102, Level 15, State 1, Procedure LRP_TDO_Solicitation_Goals, Line 202

Incorrect syntax near ')'.

Msg 156, Level 15, State 1, Procedure LRP_TDO_Solicitation_Goals, Line 204

Incorrect syntax near the keyword 'GROUP'.

With charindex:

where

 

tsol.solicitor in (charindex(',' + convert(varchar, solicitor) + ',' , ',' + @solicitor + ',') > 0)  and tsol.campaign_no in (select campaign_no from t_campaign where category = 32 and fyear = @fyear)

Error:

Msg 102, Level 15, State 1, Procedure LRP_TDO_Solicitation_Goals, Line 202

Incorrect syntax near '>'.

Msg 156, Level 15, State 1, Procedure LRP_TDO_Solicitation_Goals, Line 204

Incorrect syntax near the keyword 'GROUP'.

Any suggestions?

-James

Parents
  • Former Member
    Former Member $organization

    Hi James

    I'm not sure whether you managed to solve this, but take a look at the Patron Activity Report which uses similiar code with the Host parameter.

    Something like this works:

    declare @solicitor varchar(255)

    select @solicitor = ('"name1","name2"')

     

    select * from T_CONTRIBUTION 

     where charindex(',' + convert(varchar,rtrim(solicitor)) + ',' , ',' + replace(@solicitor,'"','') + ',') > 0 

     

     

     

Reply
  • Former Member
    Former Member $organization

    Hi James

    I'm not sure whether you managed to solve this, but take a look at the Patron Activity Report which uses similiar code with the Host parameter.

    Something like this works:

    declare @solicitor varchar(255)

    select @solicitor = ('"name1","name2"')

     

    select * from T_CONTRIBUTION 

     where charindex(',' + convert(varchar,rtrim(solicitor)) + ',' , ',' + replace(@solicitor,'"','') + ',') > 0 

     

     

     

Children
No Data