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

 

[pagino]

Jay ..
If I take the double quotes out I get the following error:
Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'developer'. Expected ''''' in search condition 'painter developer'.

 

[pagino]

Bamm ..
Got it work with freetext ..
Thanks a lot guys .. I learned a bunch here ..

Cheers

 

[pagino]

FYI .. print out of query which is working, now I'll move into making it better:

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 freetext (a.jobTitle,
'painter assistant developer')
OR freetext (a.jobDuties,
'painter assistant developer')
OR freetext (a.jobBenefits,
'painter assistant developer')
OR freetext (a.jobFullDescription,
'painter assistant developer')
OR freetext (g.jobCategory,
'painter assistant developer')
OR freetext (h.educationLevel,
'painter assistant developer')
AND e.employerName like
'%'
AND a.jobTypeId like
'%'
AND a.jobCategoryId like
'%'
AND b.countryId like
'%'
order by a.createDate desc

 

[mrdenny]

Excellent, I'm glad it's finely working.

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)