COMPLEX STORE PROCEDURE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

COMPLEX STORE PROCEDURE

Author

Topic

sahoong
Starting Member

1 Post

Posted - 2007-12-12 : 16:30:31

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 SAINSBURYS - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURYS 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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 16:36:43

So how far did you get on this homework assignment? We don't help out with these until we've some work on your part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-12 : 16:36:47

homework/exam time at school again?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-12 : 16:37:06

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 16:42:50

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-12 : 16:48:53

That sounds like a take home final exam for a database design/development class. Funny stuff. To bad the internet wasn't really around when I was taking those classes.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-13 : 03:52:11

Hope you listened to your teacher this year.

uberman
Posting Yak Master

159 Posts

Posted - 2007-12-13 : 06:29:26

And a quick google turned up http://www.daniweb.com/forums/thread100865.html.. kinda looks familiar!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources