Full Text indexing : - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Full Text indexing :

Author

Topic

vchandm23
Starting Member

5 Posts

Posted - 2012-07-25 : 20:10:54

I have created a full text indexing on a table called Person.Contact

I want my select statement to be like this,

Create Proc (@par1 VARCHAR(200),@SearchValue VARCHAR(300))
AS
SELECT * FROM Person.Contact
WHERE CONTAINS((@par1), @SearchValue)

@par1 can be (Firstname,lastname, city)
@Searchvalue can be (Chistopher,Nolan, Gotham)

I know where I should actually search for the values, i mean the columns on which i have to do my search. Therefore, if there are two values entered in the two textbox field of the application, Then I want to search those two values in those particular columns alone.
In short I want to determine where to search the values in the full text indexed table.

How can I do this? By the way the above script is not working? But I want something to happen like that is shown above.

cheers,
Chandra

vchandm23
Starting Member

5 Posts

Posted - 2012-07-25 : 23:19:27

Hi All,

I admit that my question is vague and now I have fine grained my problem. let us keep this question simple.

The stored procedure shown below is not possible after searching in google ...

---------------------- forget about this sp -------------------
Create Proc (@par1 VARCHAR(200),@SearchValue VARCHAR(300))
AS
SELECT * FROM Person.Contact
WHERE CONTAINS((@par1), @SearchValue)

----------------------- SP ends here --------------------------

For get the above stored procedure .... Now in the below SP shown

--------------------------(2)-------------------------------
Create Proc (@WhereClause VARCHAR(1000))
AS
SELECT * FROM Person.Contact
WHERE @WhereClause

--------------------------(2) ends here --------------------

From the application I want to determine the where clause combination.
Reason is simple I know before hand on which column I have to search the data on Full text indexed table.

For example : If my application passes @WhereClause = "Contains(Firstname,'Christopher') AND Contains (Lastname,'NOLAN') AND
Contains(City,'Gotham')"

Then my stored procedure should apply this @WhereClause in the WHERE portion of the statement specified in (2)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-26 : 23:53:11

You have to use dynamic SQL. This is sucky for "purity" reasons and dangerous from an SQL injection perspective if not done right.
If you have a limited number of combinations then you're better off writing one SP for each case, or major case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources