keyword search in dynamic stored procedure 2

[pagino]

Hello Gang ..
I have a dynamic stored procedure which needs to be able to process as part of it's search a form field which may contain several words seperated by a space. ie: earth diamonds brazil ocean

My dynamic stored procedure works great, right now the parameter containing the form input is a treating the entire entry as a string without breaking it up. I wonder if anyone here can help .. here is what my sp looks like right now:

CREATE PROCEDURE sp_JobSearch

(
@keyWords varchar(100),
@companyName varchar(50),
@jobType varchar(10),
@jobCategory varchar(10),
@country varchar(10),
@state varchar(10),
@province varchar(50),
@city varchar(50),
@order varchar(20)
)

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Declare
@sql varchar(8000)

Set @sql = 'Select' + char(10) + 'a.jobId,' + char(10) + 'a.keyWords,' + char(10) + 'a.jobShortDescription,' + char(10) + 'a.jobTypeId,' + char(10) + 'a.jobCategoryId,'
+ char(10) + 'a.employerId,' + char(10) + 'a.createDate,' + char(10) + 'b.city,' + char(10) + 'b.stateId,' + char(10) + 'b.province,'
+ char(10) + 'b.countryId,' + char(10) + 'c.stateAbreviation,' + char(10) + 'd.countryAbreviation,' + char(10) + 'e.employerName,'
+ char(10) + 'f.jobType,' + char(10) + 'g.jobCategory,' + char(10) + 'a.jobDuties,' + char(10) + 'a.jobBenefits,' + char(10) + 'a.jobFullDescription,'
+ char(10) + 'a.jobSalaryLow,' + char(10) + 'a.jobSalaryHigh,' + char(10) + 'h.educationLevel' + char(10) +
'from tblJobs a' + char(10) +
'LEFT OUTER JOIN tblEmployers e ON a.employerId = e.employerId' + char(10) +
'LEFT OUTER JOIN tblEmployerLocations b ON a.jobLocationId = b.locationId' + char(10) +
'LEFT OUTER JOIN tblCountries d ON b.countryId = d.countryId' + char(10) +
'LEFT OUTER JOIN tblStates c ON b.stateId = c.stateId' + char(10) +
'LEFT OUTER JOIN tblJobTypes f ON a.jobTypeId = f.jobTypeId' + char(10) +
'LEFT OUTER JOIN tblJobCategories g ON a.jobCategoryId = g.jobCategoryId' + char(10) +
'LEFT OUTER JOIN tblEducationLevels h ON a.jobEducationLevelId = h.educationLevelId' + char(10) +
'where
getdate() between a.jobStartDate and a.jobEndDate AND
a.active = 1 AND a.deleted = 0 AND a.statusId = 2 AND
e.active = 1 AND e.deleted = 0 AND e.employerStatusId = 2 ' + char(10)

if @keyWords = ''
begin
set @sql = @sql + 'AND a.keyWords like ' + char(10) + '''%''' + char(10)
end
else
begin
set @sql = @sql + 'AND a.keyWords like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR a.jobDuties like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR a.jobBenefits like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR a.jobFullDescription like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR g.jobCategory like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR h.educationLevel like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)
end
if @companyName = ''
begin
set @sql = @sql + 'AND e.employerName like ' + char(10) + '''%''' + char(10)
end
else
begin
set @sql = @sql + 'AND e.employerName like' + char(10) + '''' + '%' + @companyName + '%' + '''' + char(10)
end

if @jobType != ''
begin
set @sql = @sql + 'AND a.jobTypeId = ' + char(10) + @jobType + char(10)
end
else
begin
set @sql = @sql + 'AND a.jobTypeId like ' + char(10) + '''%''' + char(10)
end
if @jobCategory != ''
begin
set @sql = @sql + 'AND a.jobCategoryId = ' + char(10) + @jobCategory + char(10)
end
else
begin
set @sql = @sql + 'AND a.jobCategoryId like ' + char(10) + '''%''' + char(10)
end
if @country = ''
begin
set @sql = @sql + 'AND b.countryId like ' + char(10) + '''%''' + char(10)
end
else
begin
set @sql = @sql + 'AND b.countryId = ' + char(10) + @country + char(10)
end

if @state != '' AND @province = ''
begin
set @sql = @sql + 'AND b.stateId = ' + char(10) + @state + char(10)
end
if @province != '' AND @state = ''
begin
set @sql = @sql + 'AND b.province like ' + char(10) + '''' + '%' + @province + '%' + '''' + char(10)
end

if @city != ''
begin
set @sql = @sql + 'AND b.city like ' + char(10) + '''' + '%' + @city + '%' + '''' + char(10)
end

if @order = 'createDate'
begin
set @sql = @sql + 'order by a.createDate desc'
end
else if @order = 'relevance'
begin
set @sql = @sql + 'order by a.keyWords'
end

exec (@sql)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

 

[mrdenny]

This is a perfect use of full text searching. Check BOL or let me know if you need more info.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pagino]

Thanks Denny ..
I'm reading through all the documents right now, but I'm yet to find a case that would apply it in a dynamic stored procedure however .. do you have any examples ..

 

[JayKusch]

I think what Denny is inferring is that you leverage Full-Text Indexing on your tables and forgo using this Dynamic SQL statement.

Dynamic SQL has its place, but it does incurre quite a bit of overhead in the resouce utilization area as well as opens you up to SQL injection attacks.

Thanks

J. Kusch

 

[mrdenny]

ok, now that I've looked through the code a little more, you'll still need the dynamic sql because of some of the stuff in your whare clause.

You'll want to replace the following

set @sql = @sql + 'AND a.keyWords like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10) 
set @sql = @sql + 'OR a.jobDuties like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR a.jobBenefits like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR a.jobFullDescription like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR g.jobCategory like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10)
set @sql = @sql + 'OR h.educationLevel like' + char(10) +  '''' + '%' + @keyWords + '%' + '''' + char(10)

with full text search code. Otherwise you'll need to break the code apart using substring and charindex and putting lots of extra lines of code in.

I'm thinking that will be your best bet. Sorry I posted not so helpfull advise first.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pagino]

Thanks Jay ..
I'm new to Full Text Searches .. so pls forgive me as I may seem disoriented ... anyhow, the stored procedure you see here takes data from a form which sends more than one parameter per request to the stored proc and those parameters can be integers and varchars. My question is, will the full text search help me here?
Also .. if I'm not able to get full text search implemented on our sql server due to access or lack of admin resources .. then What other options do I have to accomplish what I'm looking for?

 

[mrdenny]

Full text search takes a plain text search string like "rock paper water" and uses that to search the record for records that have one or more matches, then ranks then accordingly.

If you don't use full text search, you'll have to use PATINDEX and SUBSTRING to break apart the paramater with the list of paramaters into each word, and create a seperate line of the where clause for each one.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JayKusch]

Then taking MrDennys' example ... you may run into an issue where you have a compound word like "street sign". Using PatIndex and Substring may parse the fields out in a manner that was not intended for the look up.

Here you would not know wether to break it up into "street" and "sign" or keep it combined as in "street sign". As you can see, it get complicated.



Thanks

J. Kusch

 

[pagino]

Excellent ..
This is exactly what I needed .. I'll make my case to get full text search going in here ..
Thanks a lot for all the help ..

 

[pagino]

Ok guys ..
Got my full text search catalog created, I called it SearchKeyWords ..
Any tips on how to go about calling it from within my dynamic stored proc ?

 

[mrdenny]

You'll want to lookup CONTAINSTABLE in BOL.

The basic syntax is:

USE Northwind
GO
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN 
   CONTAINSTABLE(Categories, Description, 
   'breads fish wine') AS KEY_TBL
   ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pagino]

Gang .. sorry to bother again ..
I've tried the following in my dynamic stored proc::

Changed line that currently reads:
set @sql = @sql + 'AND a.keyWords like' + char(10) + '''' + '%' + @keyWords + '%' + '''' + char(10)

To:
set @sql = @sql + 'AND contains (a.jobTitle,' + char(10) + '''' + @keyWords + '''' + ')' + char(10)

BUT I'm not getting any returns, no errors either ..

 

[JayKusch]

Have you put a print statement in to see what your code looks like after you have strung it together. I can see that "maybe" you need a space between your ' and AND comment as in ... also add the print statement as shown below too.


set @sql = @sql + ' AND contains (a.jobTitle,' + char(10) + '''' + @keyWords + '''' + ')' + char(10)

PRINT @sql

Thanks

J. Kusch

 

[pagino]

Ok .. so there actually was no problem with the statement.
However if more than one word exists in the parameter of @keyWords, then I get the following error::

@keyWords = Developer Cook

Error: Syntax error occurred near 'Cook'. Expected ''''' in search condition 'Developer Cook'.

 

[mrdenny]

Can you print the finished query as Jay recommended, and post the query so we can take a look at it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pagino]

Ok .. I got the error to go away, and the keyWords I'm passing are: painter developer
I thought that for full text search, it would go and find all records that have either one of those two keyWords, I'm I wrong about this ?

CODE:

Select
a.jobId,
a.jobTitle,
a.jobShortDescription,
a.jobTypeId,
a.jobCategoryId,
a.employerId,
a.createDate,
b.city,
b.stateId,
b.province,
b.countryId,
c.stateAbreviation,
d.countryAbreviation,
e.employerName,
f.jobType,
g.jobCategory,
a.jobDuties,
a.jobBenefits,
a.jobFullDescription,
a.jobSalaryLow,
a.jobSalaryHigh,
h.educationLevel
from tblJobs a
LEFT OUTER JOIN tblEmployers e ON a.employerId = e.employerId
LEFT OUTER JOIN tblEmployerLocations b ON a.jobLocationId = b.locationId
LEFT OUTER JOIN tblCountries d ON b.countryId = d.countryId
LEFT OUTER JOIN tblStates c ON b.stateId = c.stateId
LEFT OUTER JOIN tblJobTypes f ON a.jobTypeId = f.jobTypeId
LEFT OUTER JOIN tblJobCategories g ON a.jobCategoryId = g.jobCategoryId
LEFT OUTER JOIN tblEducationLevels h ON a.jobEducationLevelId = h.educationLevelId
where
getdate() between a.jobStartDate and a.jobEndDate AND
a.active = 1 AND a.deleted = 0 AND a.statusId = 2 AND
e.active = 1 AND e.deleted = 0 AND e.employerStatusId = 2
AND contains (a.jobTitle,
'"painter developer"')
AND e.employerName like
'%'
AND a.jobTypeId like
'%'
AND a.jobCategoryId like
'%'
AND b.countryId like
'%'
order by a.createDate desc

 

[mrdenny]

It should break it apart and search on each one. I take the contains isn't working correctly for you?

You can also try the FREETEXT keyword instead of contains and see if it works any better for you?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JayKusch]

First off it looks like you have double quotes and single quotes around your criteria. It should only have single quotes.

'painter developer'

And I am not sure what you are trying to accomplish w/ all your other search values looking for '%'?

Please explain.

Thanks

J. Kusch

 

[pagino]

Jay ..
You are seeing wildcards in the other search values because the user didn't select or enter anything into the form input, so I'm just getting all records .. I do plan on going back in cleaning it up a lot .. but at this time I'm mostly concerned with the full text search not giving me the expected results ..
I'll put to work mrDenny's advise and see how it goes ..
By the way .. I really appreciate everyone's time on helping me with this ..

 

[JayKusch]

So did removing the two " help w/ the query ????

Thanks

J. Kusch