Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbkayne
Posting Yak Master
100 Posts |
 Posted - 2002-09-27 : 20:42:31
|
| I would like to pass 1 parameter to a stored procedure that contains a string of keywords. A match will only occur if every word(inflectional) is in the row. Order does not matter. After looking at BOL, the only way I could accomplish this was through dynamic sql. Here is a code snippit of the resulting where clause:....where contains(sale.*, ' FORMSOF (INFLECTIONAL, "boat") ') and contains(sale.*, ' FORMSOF (INFLECTIONAL, "car") ')Please advise me if using dynamic sql is the only solution to this problem. If so, I would be curious to know if there is any way in this scenario, to grant execute privileges to the procedure, but not select privileges to the underlying table.Thank you!Edited by - jbkayne on 09/27/2002 20:43:45 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-28 : 18:00:09
|
| I tend to use a UDF for this. This also allows me to remove any noise words as well (some would argue that this is best left on the client side and I don't disagree, its just that I control the noise word list so if I make changes I can reflect them without worrying about the front end - I have a single column noise word lookup table)Use fn_Split to tokenise the string of keywords,join to the noise words to remove them and then use COALESCE to form a properly formatted FTS search phrase.I'll post an example on Monday - but this should give you the idea - there is certainly no need for dynamic sql.HTHJasper Smith |
 |
|
|
jbkayne
Posting Yak Master
100 Posts |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-30 : 09:35:28
|
You're more resourcefull than me - I didn't even remember I'd posted it  HTHJasper Smith |
|
|
|
|
|
|
Full Text Search T-SQL Challenge