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
It can also be useful to strip out the double-quotes prior to filtering in the WHERE clause or JOIN.
Select @solicitor = Replace(@solicitor, ‘”’, ‘’)
(Outlook as reformatted those quotes, so a copy/paste into SSMS will need some tinkering)
UserId is stored as a char(8), so the value is padded when it is less than 8 characters. Rtrim(solicitor) in the clause ensures that “user “ (note 4 spaces) is evaluated as “user”.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Annie EdlichSent: Monday, July 04, 2011 9:42 PMTo: Ryan CrepsSubject: Re: [Tessitura Technical Forum] Custom report multi-select stor proc problem
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
From: James Fairfield <bounce-jamesfairfiled8203@tessituranetwork.com>Sent: 6/10/2011 10:10:05 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!