Multiple Keyword search

Question

Multiple Keyword search

Darren Wainwright

SSC Veteran

Points: 240
More actions
September 10, 2006 at 2:48 am

#91781

Hi Everyone,
This is my first post here -so, first thing - Hi 🙂
I am developing a new website, a job portal. I usually write my SQL inline in the page but am now moving from classic asp to asp.net and am taking the time to learn about stored procedures too. So far so good, everything seems to be going well - up to this point...
The main function of the site is obviously to search a database. As i say, i would normally put the SQL inline and create an array to build the search string. What i would like to know is how can i do this in a stored procedure? Ie search for Web Developer in the job title, and the results would be anything with WEB, WEB DEVELOPER, WEB AND DEVELOPE, WEB OR DEVELOPER etc.
any help would be great.
Regards
Darren

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76035 More actions · Answer 1

I would start reading up about full-text indexes in SQL Server.

I would also look at the Thunderstone website for their Webinator search engine. It is a spider, which means it crawls through all the links on your site.

Because Webinator is a full-blown search engine it's hit rate is likely to be far better.

Full text searches can also be very effective because you get search engine like functionality with lower overheads than coding the same thing up in traditional indexes.

LinkedIn Profile

Darren Wainwright SSC Veteran Points: 240 More actions · Answer 2

Hi David,

Thanks for your reply. I really do not want to go down the route of having a third party tool like webinator, besides it being grossly overpriced for what i would need it takes away the ability to have total control over your own code.

Full text search may be an option, i will look into this more, though in the meantime i would really like to learn more about stored procs.

Thanks again anyway.

Regards

Darren

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 3

One possibility is to delegate the query string building down to the stored procedure instead of doing it in your application code. You'd just pass in the search string as a parameter to the procedure and do you query building logic to build the query in a variable, then use

EXEC

dbo.sp_executesql @statement = @yourbuiltstatement

before the end of your stored procedure to execute it.

However, that's losing any performance gains you get by using a stored procedure, as the cached execution plan will most likely be invalid each time and will have to be rebuilt. However, you do still get the advantages of proper separation of logic between the database and application, as well as hiding the logic from snoopers and being able to apply SQL security and access strategies to only allow access to the users you want.

Another option would be to specify limits on the size and type of query that can be passed in and have a procedure which implements all possible variations, with logic to call the appropriate query (this could be split up into sup-procedures if you prefer, which does have performance advantages).

PhilM99 SSCrazy Points: 2214 More actions · Answer 4

Maybe this is what you need to know:

In SQL Server Management Studio, select your database, got to Progammability..Stored Procedures... right mouse, 'new stored procedure'.

In the tab that opens, note that you can use Ctrl shift M to put in some basic information for the new stored proc.

For your parameter, you need a name (such as srchdata) and a data type ( maybe varchar(50) ).

In the stored proc, all you need is a SELECT statement similar to the one you would have used in asp.

SELECT * FROM TableofJobData WHERE ColumnName Like '%' + srchdata + '%'

(% is the wildcard character).

You can put more than one parameter in and use it in the WHERE statement if it is not Null.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

You should be aware that if you use dynamic SQL in your stored procedure, it will be executed in the security context of the user running the stored procedure, and not that of the owner of the stored procedure. Therefore, if you go down this road, you will need to grant permissions on the underlying tables.

John

Darren Wainwright SSC Veteran Points: 240 More actions · Answer 6

thanks for your help guys.

I think the best practice in this sort of case would be for me to build the string and then pass it in as a variable and execute it then.

Rookie, thanks for the help, however i can already do this, my problem was splitting multiple keywords up and creating a string with multiple like clauses.

Thanks again guys

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Darren

I recommend that you read this before proceeding, so that you are aware of any pitfalls of whatever method you choose.

http://www.sommarskog.se/dynamic_sql.html

John