quote:

---

Originally posted by financemanager

I have the following to answer, i have never done this can somebody help please...........


How do you write a store procedure to meet the following conditions:

PROXIMITY SERACH PROCEDURE

COMPANY table

PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int

COMPANY_KEYWORD table

PK, FK1 company_id int
PK keyword varchar(100)



The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,

A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.

proximity_search
@search_expression varchar(255)
,@x_loc int
,@y_loc int
,@max_distance int
,@max_records int


Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT - Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.


Distance can be calculated using the following formula:

Distance = sqrt( square(x1-x2) + square(y1-y2) )

THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be appropriate.

---