May 19, 2006 at 10:51 am
Hi everyone,
I want to create search engine in a table has 7 column nvarchar type.Then in my form, I create a combobox to select field, next is a textbox to type keyword, and another combobox wich values (AND,OR,NOT), then user can recursive any time with another field or even same previous field but with anther keyword. The query for example could look like this:" select somefields from tblMytable where field1 like N'%keyword1%' and field1 like N'%keyword2%' or field2 like N'%keyword3%'...". How I can do that in SP wich it has checked the keywords for security. I mean, how to pass the fields selected and the keywords that user typed in each phrase that is not to use EXEC('query string') in the SP.
Show me please, thank very much.
May 21, 2006 at 2:00 pm
Do you allow them to specify parentheses as well? If not, they are likely to get some unexpected results if they mix AND with OR - especially since AND is evaluated before OR. And how can NOT be in the same combobox as AND,OR? NOT is a unary operator and can't be used to join two conditions together.
These problems aside, if the user can add any number of conditions and connect them with a boolean operator of their choice, then I don't think there will be a sensible solution that doesn't use dynamic SQL (EXEC() or sp_executesql). If a solution using only AND is acceptable, you can do it, but I'm not sure that you would want to even then.
Why don't you want to use dynamic SQL?
Tim.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply