Freetext Search with Parameter

Hi All,
I am trying to perform a simple search but not sure what the best way is
to do it.
In my stored procedure I have 3 parameters.
1. Newspapers (if param is 0 search all)
2. Companies (if param is 0 search all)
3. Keywords
Is the only way to do these queries by using dynamic sql?
e.g.
@.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
if @.Companies > 0
begin
@.SQL = @.SQL + ' and a.companies = ' + @.Companies
end
etc.
Can anyone help?
Thanks
Angela
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Angela,
If I correctly understand what you're trying to accomplish, then using a
IF... THEN... ELSE construct might work for you, for example:
-- FTS and IF/THEN/ELSE construct
Declare @.searchstring varchar(255)
if(@.searchstring = '1')
SELECT * FROM Article
else
SELECT * FROM Article
WHERE FREETEXT(name, @.searchstring)
-- and so, on...
If the above doesn't work for you, then try using a CASE statement. If
possible, could you post your stored proc code?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eYM#MGgDFHA.2572@.tk2msftngp13.phx.gbl...
> Hi All,
> I am trying to perform a simple search but not sure what the best way is
> to do it.
> In my stored procedure I have 3 parameters.
> 1. Newspapers (if param is 0 search all)
> 2. Companies (if param is 0 search all)
> 3. Keywords
> Is the only way to do these queries by using dynamic sql?
> e.g.
> @.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
> if @.Companies > 0
> begin
> @.SQL = @.SQL + ' and a.companies = ' + @.Companies
> end
> etc.
> Can anyone help?
> Thanks
> Angela
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for the reply John,
Here's my SP so far, which works but is there another way (i.e. not
using dynamic sql). On my asp page, if the user select 'Search ALL
companies' then a 0 is passed on to the SP. And like wise with
'Newspapers'
----
DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
DECLARE @.SQL varchar(8000)
SET @.SQL = ''
SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
@.Keyword)'
IF @.CompanyID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
varchar)
END
IF @.NewspaperID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
varchar)
END
EXEC @.SQL
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Angela,
I've not forgotten this issue, as I'm currently working on other issues at
this time. Could you also post the sp_help Article output and a small sample
of the data?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eY0IypoDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply John,
>
> Here's my SP so far, which works but is there another way (i.e. not
> using dynamic sql). On my asp page, if the user select 'Search ALL
> companies' then a 0 is passed on to the SP. And like wise with
> 'Newspapers'
>
> ----
> DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
> DECLARE @.SQL varchar(8000)
>
> SET @.SQL = ''
> SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
> @.Keyword)'
> IF @.CompanyID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
> varchar)
> END
> IF @.NewspaperID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
> varchar)
> END
> EXEC @.SQL
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!