Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: Complex Store Procedure

    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.

  2. #2
    Join Date
    Dec 2006
    Posts
    30
    Smells like homework.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i think it might be finals week. it must be a take home exam.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nerdler
    Smells like homework.
    you think?

    see http://forums.devshed.com/ms-sql-dev...re-495645.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Wink Death Goddess

    Quote Originally Posted by r937

    Figures.

    By the way, I noticed you have 15,000+ posts on this forum and 10,000+ on dbForums. Very impressive!

    (You should take a week off and spend more time with your wife )
    Wow thats allot of posting

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and 21,000+ on sitepoint

    i'm divorced so i have lots of time

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's like a parallel universe thread.

    (I hope someone posts the same over at t'other forum)

  8. #8
    Join Date
    Sep 2007
    Posts
    2

    Attempt

    I have attempted it.

    This is what i've done so far, any lead?

    CREATE PROCEDURE proximity_search @max_records int

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

    AS

    SET NO COUNT ON

    DECLARE @company_id INT
    DECLARE @company_name VARCHAR(100)
    DECLARE @premise VARCHAR(10)
    DECLARE @street VARCHAR(30)
    DECLARE @town VARCHAR(30)
    DECLARE @postcode CHAR(7)
    DECLARE @x_loc INT
    DECLARE @y_loc INT

    SELECT company_name, premise, street, town, postcode FROM company


    IF @search_expression = 'TESCO AND CASHPOINT' AND @max_distance =



    CREATE FUNCTION Distance
    --- input dimensions in Metres
    (@x_loc (x1,x2), @y_loc (y1,y2))
    RETURNS int --- Square Metres
    AS
    BEGIN
    RETURN (sqrt( square(x1-x2) + square(y1-y2))
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •