Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    28

    Unanswered: Use value from one select within a stored procedure

    Hello!

    How do you use the value of one select statement within another select statement inside the same stored proc?

    Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!

    I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.

    Any suggestions?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If I'm getting you right...

    SELECT * FROM myTable99 o
    WHERE Something
    AND EXIST(SELECT * FROM myTable99 i WHERE SomethingElse
    AND o.key = i.key)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2004
    Posts
    28
    Using your example I came up with this, but it didn't work.

    SELECT person_row_id,last_name, first_name, SSN
    FROM t_person a
    WHERE ssn = '123456789'
    AND EXIST(select address_1, address_2, city,state_province, client_employee_id
    from t_person_by_service b where person_row_id = a.person_row_id)

    I'mnot sure if that helps you understand what I am trying to do, but if it does, what I am looking for in a perfect world would be to return this as one table, not two

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Happy Holidays

    Code:
        SELECT  a.person_row_id
    	  , a.last_name
    	  , a.first_name
    	  , a.SSN
    	  , b.address_1
    	  , b.address_2
    	  , b.city
    	  , b.state_province
    	  , b.client_employee_id
          FROM t_person a
    INNER JOIN t_person_by_service b
    	ON a.person_row_id = b.person_row_id
         WHERE a.ssn = '123456789'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2004
    Posts
    28
    That worked! But, it took 11 seconds to return. If I make the 2 calls separately in my code, it takes less than 1 second.

    Any ideas??

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What indexes do you have?

    I would have one with ssn, person_row_id for t_person

    And another just on person_row_id for t_person_by_service
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2004
    Posts
    28
    Holy cr*p that made a big difference!!

    Thank you so much! I really appreciate it!

  8. #8
    Join Date
    Sep 2004
    Posts
    28
    I already posted this once but it has not turned up in 40 minutes

    That made all the difference.

    Thank you very much! I really appreciate it!

Posting Permissions

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