keyword search in dynamic stored procedure

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
GODynamic SQL is a sure sign of a bad programmer. It says that you had
no idea what you were doign until a user told you at run time. It
says that you have a schema that is so screwed up you need to invent
things on the fly.
as part of it's search a form field which may contain several words
seperated by a space.<<
Forms? Fields? Those do not exist in SQL; you are still writing file
system code. You even put those silly redundant "tbl-' prefixes on
table names! And you use absurd names like "jobTypeId" or "StatusId",
which makes no sense (types are not identifiers, status oif what' --
think about it). You camelcase it to make it harder to read. And
where is you DDL? What names are VARCHAR(50) -- can you give one
example? Why are you using flags in SQL? Why do you think that "!="
is Standard SQL? Why do you think that alphabetizing the PHYSICAL
appearance of tasble names in a FROM clause will give you a meaningful
alias for the base tables.
Frankly, after almost 20 years with SQL, this is close to the worst
code I have seen. Certainly, the top ten worst.|||--CELKO-- wrote:
[snip]
> Frankly, after almost 20 years with SQL, this is close to the worst
> code I have seen. Certainly, the top ten worst.
Ooh. I'd love to see #1, if you can find/post it? :-)|||--CELKO-- wrote:
> What names are VARCHAR(50) -- can you give one
> example?
Erm, a company name? The UK Government Data Standards Catalogue notes
that Organisation Name was 'amended as the result of discussion at
Schema Group on 21 August 2002-09-21 from 70 characters to 255
characters.'
http://www.govtalk.gov.uk/gdsc/html/frames/default.htm
Here's one I found fairly quickly by searching on the bureaucracy's own
web site (http://www.companies-house.gov.uk/):
WEST MIDLANDS AND SHROPSHIRE CO-OWNERSHIP HOUSING SOCIETY (OAKS
CRESCENT) LIMITED
Jamie.|||Damien wrote:
> --CELKO-- wrote:
> Ooh. I'd love to see #1, if you can find/post it? :-)
Google the exact phrase, "This is the worst use of SQL I have ever
seen"
Jamie.|||ROTFL
onedaywhen wrote:
> Damien wrote:
>
>
> Google the exact phrase, "This is the worst use of SQL I have ever
> seen"
> Jamie.
> --
>|||CELKO ..
My web app executes this dynamic stored proc .. (that's where the forms and
fields come from).
Sorry you didn't like the naming schemes and I'm not sure why you are
making so many assumptions and judging the code ..
Anyhow .. could you post the best SQL code you've ever seen and did you
write it?
Message posted via http://www.webservertalk.com|||onedaywhen wrote:
> Damien wrote:
worst
> Google the exact phrase, "This is the worst use of SQL I have ever
> seen"
> Jamie.
> --
Well, that cleared that one up.
Well, I've just had confirmation from Amazon.com that SQL Programming
Style has shipped. Had to pay international shipping since Amazon.co.uk
aren't stocking it yet :-(
Damien|||>> I'd love to see #1, if you can find/post it? <<
#1? A shortened two-page version of it is in SQL PROGRAMMING STYLE. in
Section 6.1. It is a report from an accounting system built with
UNIONs in dynamic SQL that overflows the size limits of SQL Server.
Every account that appears in the report was done as a separate SELECT
statement witht eh same WHERE clause then UNIONed (not even UNION ALL)
into final result that was used to compute some totals. You can do it
in one statement with SUM(CASE..) constructs.
#2 was a "One True Lookup Table" in which the guy tried to fix it by
adding the needed CHECK() constraints. He had a case expression with
almost fifty "WHEN code_type = ' AND code_value = ' THEN 1 ELSE 0"
clauses on it. Since all the codes were in that one table and it had
a clustered index, the disk drive had to jump from one end of the table
to another for each row in the result set. Ever seen an out-of-balance
washing machine?
#3 was a VB programmer who was given no training and no help. He wrote
one update statement per column everywhere in a moderately sized
procedure for an educational testing service. He honestly did not know
the syntax for multi-column updates. I removed several hundred lines
of code and made it run 2300 times faster. I am good -- maybe 2-3
orders of magnitude improvement if the code stinks, but I am not that
good; he was that bad.|||>> Well, I've just had confirmation from Amazon.com that SQL Programming Sty
le has shipped. Had to pay international shipping since Amazon.co.uk aren't
stocking it yet :-( <<
Bummer! Barnes & Noble in States is pretty good about getting MKP
stuff to the shelf, but I don't know about the UK and Europe.