CODEWITHSUNDEEP
sqlsql-serverstored-procedureskeyword-search
Aya
Aya

Reputation: 53

Keyword SQL Server stored procedure

I am fairly new to SQL Server but I have to write a stored procedure that will search a specific table with a list of keywords and it is suppose to return the rows which a hit has been found, I wrote a query which works but the problems is when I have to amend the keyword list then I have to start from scratch with writing the query.

The query is as follows 

SELECT * 
INTO [playground].[dbo].[New table name] 
FROM [playground].[dbo].[Main table]
WHERE [Document Type Description] LIKE 'Alcohol' 
   OR [Document Type Description] LIKE 'DSTV' 
   OR [Document Type Description] LIKE 'Entertainment' OR
[Document Type Description]like'Bday' OR
[Document Type Description]like'Birthday' OR
[Document Type Description]like'Bar' OR
[Document Type Description]like'Booze' OR
[Document Type Description]like'Catering' OR
[Document Type Description]like'Farewell' OR
[Document Type Description]like'Food' OR
[Document Type Description]like'Function' OR
[Document Type Description]like'Meals' OR
[Document Type Description]like'Year end functions' OR
[Document Type Description]like'Womens day' OR
[Document Type Description]like'Womans day' OR
[Document Type Description]like'Tuck shop' OR
[Document Type Description]like'Teambuilding' OR
[Document Type Description]like'Refreshment' OR
[Document Type Description]like'Liquor' OR
[Document Type Description]like'Lunch' OR
[Document Type Description]like'Water' OR
[Document Type Description]like'Bread' OR
[Document Type Description]like'Breakaway' OR
[Document Type Description]like'Canteen' OR
[Document Type Description]like'Gifts' OR
[Document Type Description]like'Glass' OR
[Document Type Description]like'Glasses' OR
[Document Type Description]like'Glassware' OR
[Document Type Description]like'Ticket' OR
[Document Type Description]like'Rugby' OR
[Document Type Description]like'Cricket' OR
[Document Type Description]like'Tea cups' OR
[Document Type Description]like'Tea' OR
[Document Type Description]like'Sugar bowl' OR
[Document Type Description]like'Sugar' OR
[Document Type Description]like'Soup bowls' OR
[Document Type Description]like'Side plate' OR
[Document Type Description]like'Serving tray' OR
[Document Type Description]like'Saucers' OR
[Document Type Description]like'Tray' OR
[Document Type Description]like'Non slip tray' OR
[Document Type Description]like'Milk' OR
[Document Type Description]like'Milk jug' OR
[Document Type Description]like'Mugs' OR
[Document Type Description]like'Dessert' OR
[Document Type Description]like'Dessert spoons' OR
[Document Type Description]like'Dinner set' OR
[Document Type Description]like'Jug' OR
[Document Type Description]like'Kent' OR
[Document Type Description]like'Knifes' OR
[Document Type Description]like'Knives' OR
[Document Type Description]like'Cooler boxes' OR
[Document Type Description]like'Crockery' OR
[Document Type Description]like'Christmas' OR
[Document Type Description]like'Coffee' OR
[Document Type Description]like'Popcorn machine' OR
[Document Type Description]like'Cooler' OR
[Document Type Description]like'Freezer' OR
[Document Type Description]like'Fridge' OR
[Document Type Description]like'Fan ' OR
[Document Type Description]like'Extraction fan' OR
[Document Type Description]like'Heaters' OR
[Document Type Description]like'Water cooler' OR
[Document Type Description]like'Washing machine' OR
[Document Type Description]like'Warmer' OR
[Document Type Description]like'Vacuum cleaner' OR
[Document Type Description]like'Urn' OR
[Document Type Description]like'Thermostat'

Ultimately I wish that I have a SP that will read the array of keywords and let me choose which tables column to search for in the main tables

Hope this makes sense Thank you in advance

Upvotes: 3

Views: 863

Answers (1)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

A couple of ideas on your code.

The use of LIKE
The way you put your query it actually performs an equality check, as you put no wildcards in your LIKE statement. This causes two problems:

  1. You may not get the results you expect
  2. You may not be using indices (which would speed things up) because you use LIKE

First of all you need to decide whether you want to check for equality or whether the field searched should just contain the string you're looking for.

If you want an equality check, use

... WHERE [Field] = 'value'

instead of

... WHERE [Field] LIKE 'value'

Equality checks
There are a couple of way of speeding them up. You could put the search terms in a table and do something like this:

... WHERE [Field] in (SELECT Term FROM TableOfSearchTerms)

Or you could even try to join the two tables together. Then you don't need a WHERE clause at all:

... FROM Table1 t1 INNER JOIN TableOfSearchTerms terms ON terms.Term = t1.[Field]

Making the search field dynamic
This is not so easy. You could create a dynamic SQL statement as string and then use EXEC to execute this, but you have to be careful not to introduce problems (like SQL injections, etc.).

Performing an actual LIKE
In that case, you'd need to use wildcards in your statement, like this:

... WHERE [Field] LIKE `%searchterm%`

This is not so easy to solve using the things I've said above. In that case (even though it hurts me to say) it would probably be easiest to assemble a string that contains the query and execute this using EXEC. The result might look like this:

DECLARE @query NVARCHAR(max)
SET @query = "INSERT INTO ... WHERE ";

EXEC (@query)

You could use a cursor on the search terms table to add the required LIKEs to the WHERE clause.

Upvotes: 2

Related Questions