Stored Procedure

Question

Post reply

Stored Procedure

b_boy

SSCertifiable

Points: 5145
More actions
January 5, 2008 at 3:25 am

#177895

i have a scenario which i need a stored procedure to satisfy a business rule, there are two tables which are:
Student table
PK Student_id int
Student_name varchar(100)
age varchar(5)
Campus varchar(30)
city varchar(100)
zip char(7)
student_KEYWORD table
PK, FK1 student_id int
PK keyword varchar(100)
The student table holds the name and age of the students as well as a physical location in terms of an East and North co-ordinate. every student has one or more keywords or phrases associated with it in the student_keyword table. The full database holds 2 million students with an average of 4 keywords each,
am trying to create a stored procedure to provide proximity searching of the database. The procedure should return a list of students 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 students up to a maximum of @max_records.
student_search
@search_expression varchar(100)
,@x_loc int
,@y_loc int
,@max_distance int
,@max_records int
Example Search Expressions
@search_expression
keywords specified.
Philip OR David - Return all records that have one or more of the keywords specified.
philip OR david - Return all records that have one or more of the keywords specified.
Philip Perry AND Andrew
OR
David James - 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) ).
this is what i have done so far-
create procedure dbo.sp_student_search
@search_expression varchar(255),
@x_locint,
@y_locint,
@max_distanceint,
@max_recordsint
as
select student_name
from student
where student_name = 'david'
or student_name ='philip'
or student_name ='Andrew';
go

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

For the distance formula, please see your other post at the following URL...

http://www.sqlservercentral.com/Forums/Topic439224-338-1.aspx

... that should get you started...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Hello Jeff

Am a novice when it comes to stored procedures and i really need guidance, in creating this stored procedure that will include functions.

Any assistance is highly appreciated to help me learn

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I'm thinking this is for an SQL class... with that in mind, now that I've given you the distance function, you must try first... also, see the following... it will help us help you...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)