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
GOOn Thu, 19 May 2005 01:02:09 -0500, pagino wrote:

>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:
(snip)
Hi pagino,
If there's a question in your post, then I couldn't find it. I did read
that it works great right now - which is A Good Thing, as this code
looks terribly difficult to maintain...
If you're looking for generic advice, then I for the most part agree
with Celko's comments, though I would have chosen nicer words to soften
up the news.
You might find the following useful:
http://www.sommarskog.se/dyn-search.html
And maybe this as well:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo ..
After much research I opted away from the dynamic stored proc ..
But I'll have a look at your suggestions for sure ..
Thanks for taking the time to help constructively ..
Message posted via http://www.webservertalk.com|||Hugo ..
I have a question for you ..
How would you have approached this search proc, given that there is no
flexibility in terms of redoing the db schema ?
Message posted via http://www.webservertalk.com|||>> How would you have approached this search proc, given that there is no fl
exibility in terms of redoing the db schema ? <<
First of all, there are tools for doing text searching that have more
power and run orders of magnitude faster than SQL extensions. Text
searching in SQL is like putting feather on a fish.
The schema IS the real problem. Do you really have locations where you
do know the name of the country that they are in? Probably, since you
have all those expensive OUTER joins. Why is country, state, city, etc
not all attributes of location? ISince you are using ISO-3316 country
codes, why use a LIKE for it and not equality? Ditto for lots of other
things here.
What you wanted was more like this:
CREATE TABLE JobKeywords
(job_id INTEGER NOT NULL
REFERENCES Jobs(job_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (job_id, keyword));
CREATE TABLE ClientSearchWords
(client_id INTEGER NOT NULL
REFERENCES Clients(client_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (client_id, keyword));
Now do a standard Relational division to get the candidate jobs for all
clients. You can put that in a VIEW. Use a NULL for matching all
values.
SELECT M.client_id, M.job_id, L.coutnry_name, L.province_name,
L.city_name, J.eduation_level, etc.
FROM KeywordMatches AS M,
Jobs AS J,
Locations AS L,
Clients AS C,
..
WHERE L.location_id = COALESCE (M.location_id, L.location_id)
AND J.eduation_level <= COALESCE (C.eduation_level,
J.eduation_level)
AND ...;
A better way is to use a CASE expression to get a score:
( CASE WHEN J.eduation_level <= C.eduation_level
THEN 5 ELSE 0 END
+ CASE WHEN ..
+ CASE WHEN ..) AS score|||On Fri, 27 May 2005 23:07:41 GMT, Pagino via webservertalk.com wrote:

>Hugo ..
>I have a question for you ..
>How would you have approached this search proc, given that there is no
>flexibility in terms of redoing the db schema ?
Hi Pagino,
I don't really know what "this search proc" is. You haven't posted the
table structure (as CREATE TABLE statements), nor sample data (as INSERT
statements) and expected output and/or a description to explain what the
proc does. You only posted some code that is (due to extensive use of
dynamic SQL, bad formatting and line wrappings due to usenet line length
limitations) impossible to grasp in the limited time I have available
for spending in these groups.
Since I don't know the DB schema, I really can't tell if it's good or
bad. But if it is bad, then I advise you not to accept the "no
flexibility" part. Bad schemas need to be fixed. If it can't be done
now, then it HAS to be scheduled to be done ASAP. When you know that the
foundation of your house is bad, you don't accept it. You make sure that
it's fixed as soon as possible. And until it's fixed, you take whatever
measures are necessary to make sure the floor doesn't collapse - but
those are all temporary measures; the permanent solution is to fix the
foundation. Remember that the tbale design is the foundation of your
database.
Anyway, for search procedures, the usual first step is to tell the
customer what the cost (either in money or in performance, but usually
even in both) would be of implementing all their wishes. That is often
enough to convince them to tone down their wish list. Most people always
want the ability to search the DB on every column, but once it starts to
cost money, they usually have no problem to identify the three or four
search types that will actually ever be used.
The next step is to write a stored procedure for each of the search
types. The last step is to find the indexes that will speed up these
queries to the desired performance without affecting insert, update and
delete performance too much. That final step is usually the hardest :)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks guys for your replies ..
Here is what I have:
tableCountries
tableStates
tableEducationLevels
tableContacts
tableLocations
tableEmployers
tableJobs
The tableJobs will hold an integer telling it which contact, country,
state, location, employer is attributed to the job.
On the job search interface we would display something like this in a form
select:
US Texas - El Paso
US Alaska - Snowtown
So we know what the country, state and city is. The problem comes when
they select ALL locations .. I think that's how it was chosen to be handled
previously. That's why the LIKE operator was used.
The search interface requires for the user to be able to:
NOTE: If nothing is entered or chosen, then everything for that filter is
to be returned.
1. enter keywords
2. enter a company name or several
3. select job type (full time, part time)
4. select job category (managerial, administrative, etc)
5. select location (US Virginia - Fairfax, US California - Sacramento)
Here is the scripted the tables associated with the proc:
----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableCountries]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableCountries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEducationLevels]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEducationLevels]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployerContacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployerContacts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployerLocations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployerLocations]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobPayTerms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobPayTerms]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobTypes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableStates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableStates]
GO
CREATE TABLE [dbo].[tableCountries] (
[countryId] [int] IDENTITY (1, 1) NOT NULL ,
[countryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryAbreviation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEducationLevels] (
[educationLevelId] [int] IDENTITY (1, 1) NOT NULL ,
[educationLevel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployerContacts] (
[contactId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NOT NULL ,
[locationId] [int] NULL ,
[firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleInitial] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[otherPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployerLocations] (
[locationId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NULL ,
[locationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[province] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stateId] [int] NULL ,
[zipCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryId] [int] NULL ,
[createDate] [smalldatetime] NOT NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployers] (
[employerId] [int] IDENTITY (1, 1) NOT NULL ,
[employerStatusId] [int] NULL ,
[userId] [int] NULL ,
[firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleInitial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employerLogo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobCategories] (
[jobCategoryId] [int] IDENTITY (1, 1) NOT NULL ,
[jobCategory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobCategoryDescription] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobPayTerms] (
[jobPayTermId] [int] IDENTITY (1, 1) NOT NULL ,
[jobPayTerm] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobTypes] (
[jobTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[jobType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobs] (
[jobId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NOT NULL ,
[statusId] [int] NOT NULL ,
[jobTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobTypeId] [int] NULL ,
[jobCategoryId] [int] NULL ,
[jobEducationLevelId] [int] NULL ,
[jobPayTermId] [int] NULL ,
[jobSalaryLow] [money] NULL ,
[jobSalaryHigh] [money] NULL ,
[jobShortDescription] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[jobFullDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobDuties] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobBenefits] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobContactId] [int] NULL ,
[addJobContact] [bit] NOT NULL ,
[jobLocationId] [int] NULL ,
[addJobLocation] [bit] NOT NULL ,
[jobStartDate] [smalldatetime] NOT NULL ,
[jobEndDate] [smalldatetime] NOT NULL ,
[jobComments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableRoles] (
[roleId] [int] IDENTITY (1, 1) NOT NULL ,
[roleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableStates] (
[stateId] [int] IDENTITY (1, 1) NOT NULL ,
[stateName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stateAbreviation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
---
Message posted via http://www.webservertalk.com|||On Tue, 31 May 2005 23:28:05 GMT, Pagino via webservertalk.com wrote:

>Thanks guys for your replies ..
>Here is what I have:
(snip)
Hi Pagino,
First things first - let's start with the table design. There are many
things wrong. In a completely random order:
1. Why prefix all table names with "table"? What other data structures
do you expect to find in a relational database? The only thing this
achieves, is to make your code harder to read.
2. Why are almost all columns defined as "varchar(50)"? Do you really
expect to get people with up to 50 middle initials? With letters and
symbols in their phone and fax numbers? And do you really think that
you'll never have to store a URL that needs more than 50 characters?
3. Why are almost all columns NULLable? What would be the significance
of storing rows in e.g. tableCountries with countryName and
countryAbbreviation both set to NULL?
4. Why don't you have any PRIMARY KEYs? Or FOREIGN KEYs? Is data
integrity not important in your company?
5. Why the deleted and active columns? Common practice is to include
datetime columns valid_from and valid_thru - or (performansewise better)
keep old data in an audit table, and only the current data in the
regular table.
6. What is the use of the addJobContact and addJobLocation columns?
(This is not a rhetorical question like the above - I really don't
understand what you use these columns for).
In an earlier post, you said that it's not possible to redo the design.
If that is indeed true, then the best advise I can give you is to start
looking for another job. You really don't want to continue working with
a DB such as this. (Personally, I wouldn't even want to be seen dead
with it).

>On the job search interface we would display something like this in a form
>select:
>US Texas - El Paso
>US Alaska - Snowtown
>So we know what the country, state and city is. The problem comes when
>they select ALL locations .. I think that's how it was chosen to be handled
>previously. That's why the LIKE operator was used.
Check http://www.sommarskog.se/dyn-search.html for better alternatives.

>The search interface requires for the user to be able to:
>NOTE: If nothing is entered or chosen, then everything for that filter is
>to be returned.
>1. enter keywords
Check http://www.sommarskog.se/arrays-in-sql.html to find out how to
handle a delimited list in a parameter. The code you wrote won't work if
multiple keywords are entered. If I enterte as keyword "SQL Server,
design", then your query would only return rows where the EXACT TEST
"SQL Server, design" is included in any of the columns keyWords,
jobDuties, jobBenefits, etc.

>2. enter a company name or several
>3. select job type (full time, part time)
>4. select job category (managerial, administrative, etc)
>5. select location (US Virginia - Fairfax, US California - Sacramento)
I suggest that you use a drop-down box for valid job types, categories
and locations. Then simply pass either the ID of the chosen type,
category, location to the stored procedure (or pass NULL if ALL was
selected). Again: see Erland's site for much more details.
All this being said, the best advise is already given by Joe Celko: just
buuy a specialized too for this. It'lll save you lots of time and lots
of troouble, and it'll perform faster than any pure SQL Server based
alternative.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo ..
Thanks for the reply .. LOL !
I don't know why the primary and foreign keys didn't show up in the script,
they do exist, trust me. The names of the tables do not actually start with
table (I agree that would have been silly) and the columns allowing NULLS
.. well that will be changed before the last db revision is done :)
I will carry away with your suggestions however, I think they are good
suggestions and very probably the best I've gotten so far ..
In terms of tools that would do the search, which ones out there would you
recommend ?
Thanks in advanced ..
Message posted via http://www.webservertalk.com|||On Fri, 03 Jun 2005 23:48:00 GMT, Pagino via webservertalk.com wrote:
(snip)
>In terms of tools that would do the search, which ones out there would you
>recommend ?
Hi Pagino,
Since this is not a field I am familiar with, I can't do any good
recommendations on this.
A good p[lace to ask would be the fulltext group for SQL Server
(microsoft.public.sqlserver.fulltext). Maybe the people there know how
to make the fulltext search capabilities of SQL Server do this job, in
which case you don't need to buy other tools. And otherwise, they are
likely to have good recommendations where elses to look.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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.

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
TCheck out the Full Text Search features in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
T
Check out the Full Text Search features in BooksOnLine.
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

keyword / phrase searching

I have to do an app that will contain a lot of string data that will be
keywords and phrases and I will need a very fast search of that material.
Does SQL Server 2005 have any tools that are geared to this kind of search.
I don't think it has to be as fast as google but probably faster than where
clauses using "like" and "in".
Thanks,
TCheck out the Full Text Search features in BooksOnLine.
Andrew J. Kelly SQL MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:uaSeL83tHHA.3796@.TK2MSFTNGP02.phx.gbl...
>I have to do an app that will contain a lot of string data that will be
>keywords and phrases and I will need a very fast search of that material.
>Does SQL Server 2005 have any tools that are geared to this kind of search.
>I don't think it has to be as fast as google but probably faster than where
>clauses using "like" and "in".
> Thanks,
> T
>

key column information is insufficient or incorrect

Dear Friends,
I have table contain 2000 out of those some are
duplicate when i select duplicate records by using Enterprise Manager
and make modification to one of those duplicate records the following
message flashes/display.

key columen information is insufficient or incorrect.Too many rows were
affected by update

pls suggest what is this and how to solve this problem

Thanks in advance

Dinesh Patwaldinu wrote:

Quote:

Originally Posted by

Dear Friends,
I have table contain 2000 out of those some are
duplicate when i select duplicate records by using Enterprise Manager
and make modification to one of those duplicate records the following
message flashes/display.
>
key columen information is insufficient or incorrect.Too many rows were
affected by update
>
pls suggest what is this and how to solve this problem
>
Thanks in advance
>
Dinesh Patwal

Before you can edit the data in Enterprise Manager you need to add a
unique constraint or unique index. To facilitate that it may help to
use SELECT DISTINCT to eliminate duplicates and copy the data to a new
table. It rather depends on just how you want to eliminate the
duplicate data. You can Google for lots of previous posts on this topic
in this group and in the microsoft.public.sqlserver groups.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Keeping track of last time of extract

I am building an SSIS package in which I extract data from a number of transaction tables. The rows in each transaction table contain a timestamp. I would like to do the following.

* Every time I extract rows from a table I want to extract only the records added since last extract (no rows are modified or deleted - only added)
* In the extract process I want to find the maximum timestamp in a given table. This timestamp should be stored and updated in a table on the SQL Server. This way, the next time the extraction process is run only "new" rows are extracted

How would you go about doing this in SSIS? I have thought of the following approach, but I am unsure whether it is too cumbersome.

* A package variable is defined for each of the transaction tables
* A series of Execute SQL Tasks populates each of these variables with the timestamps from the table in SQL Server
* A series of data flow tasks extract the data (using the variables populated above in the WHERE-condition). Contained in the data flow tasks is a script component which records the maximum timestamp extracted and places this in yet another package variable (I am unsure how to do this, by the way...)
* A series of Execute SQL Tasks updates the table in SQL Server with the new timestamps

How would you do this?

Thanx! This is how I'd do it. It isn't particularly a SSIS solution...just the way I'd do it!

Have 2 values stored in a table called (e.g.) tblConfig. They are:
LastExtractDate
ThisExtractDate

Step 1) UPDATE tblConfig SET LastExtractDate=ThisExtractDate, ThisExtractDate = GETDATE()

Step 2) SELECT * FROM <source_table> WHERE <some_tstamp_value> >= LastExtractDate AND <some_tstamp_value> < ThisExtractDate> (this would be inside a data-flow source adapter)

OK, column names may change etc...but you get the idea!!!

-Jamie|||Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael|||

Reckless wrote:

Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael

Yeah. Good idea!

-Jamie|||Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.|||

Al_chan wrote:

Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.

Hi Al,
Step 1 I would do in an Exec SQL Task.
Step 2 most likely in a data flow - yes, using an OLE DB Data Source.

-Jamie

Keep records that contain certain attributes

Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"

suggestions?

thansk in advance

km

Hi km

I am having a bit of trouble understanding what data you are working with.
Once this is clear perhaps I can help.
Does your data-set look like the following?

Primary Key | Problem
-
1 Drugs
1 Alcohol
1 Vandalism
2 Theft
2 Drugs

And you want to display all rows with PrimaryKey = 1 (since it includes alcohol), but not any rows with Primary Key 2?

If that is the case it will definitely be easiest to do this in you query itself, not in the report.
Something like:

Select * From Table WHERE PrimaryKey IN
(SELECT PrimaryKey From Table WHERE Problem = 'Alcohol'

Cheers
Mark
|||

thanks Mark

Yes this is similiar to my data found out that there is a unique key for each atttribute so all is resolved

thanks for your response

km

Keep records that contain certain attributes

Have a report that I want to keep certain attributes and as long as the report contains this certain attribute, bring all other attributes with it. Better with an example. In this report I am specifically looking for attribute "Alcohol", if I find this attribute I want to include all others that fit with this record's Primary Key which could include, "Drugs","Arson","Vandalism", etc. Problem is when I try to use a paramater or filter I get the "Alcohol" Attribute but not the "Drugs","Arson","Vandalism", etc. Conversely since I dont have any filter/paramater set I get everything even if it does not include "Alcohol"

suggestions?

thansk in advance

km

Hi km

I am having a bit of trouble understanding what data you are working with.
Once this is clear perhaps I can help.
Does your data-set look like the following?

Primary Key | Problem
-
1 Drugs
1 Alcohol
1 Vandalism
2 Theft
2 Drugs

And you want to display all rows with PrimaryKey = 1 (since it includes alcohol), but not any rows with Primary Key 2?

If that is the case it will definitely be easiest to do this in you query itself, not in the report.
Something like:

Select * From Table WHERE PrimaryKey IN
(SELECT PrimaryKey From Table WHERE Problem = 'Alcohol'

Cheers
Mark
|||

thanks Mark

Yes this is similiar to my data found out that there is a unique key for each atttribute so all is resolved

thanks for your response

km