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'.
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)
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)
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
Incorrect syntax near '>'.
Any suggestions?
-James
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