Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    16

    Unanswered: Query as stored procedure?

    I've read a few articles about this, and trauled the net but I cant quite get one to fit my query.

    I need to make this as a stored procedure,

    select records_tbl.place_id, who_tbl.person_id, place_tbl.place_name, records_tbl.record_datetime, who_tbl.who_name, thing_tbl.thing_name
    from records_tbl, place_tbl, who_tbl, thing_tbl
    where records_tbl.place_id=place_tbl.place_id
    and thing_tbl.thing_id='TH0001'
    and who_tbl.person_id='WH000005'

    But where I can simply specify on execute the thing_id and the person_id. ie Execute query1 ('TH0001', WH000005')?

    So this would involve removing thos two id's and changing them to parameter names? defined at the top of the query? in theory I understand but in practise I do not.

    Can anyone help?

  2. #2
    Join Date
    Dec 2001
    Location
    Canada
    Posts
    45
    Create PROCEDURE MyQuery1
    (
    @thing_id VARCHAR(20)
    ,@person_id VARCHAR(20)
    )
    AS

    SELECT records_tbl.place_id,
    who_tbl.person_id,
    place_tbl.place_name,
    records_tbl.record_datetime,
    who_tbl.who_name,
    thing_tbl.thing_name
    FROM records_tbl,
    place_tbl,
    who_tbl,
    thing_tbl
    WHERE records_tbl.place_id = place_tbl.place_id
    AND thing_tbl.thing_id = @thing_id
    AND who_tbl.person_id = @person_id

    GO

    and Now call
    exec MyQuery1 @thing_id='TH0001' , @person_id= 'WH000005'

    that what you want??
    Franky
    FBoucheros@hotmail.com

  3. #3
    Join Date
    Dec 2003
    Posts
    16
    thats excellent! thanks a lot!

Posting Permissions

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