Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Unanswered: Calling stored proc B from stored proc A

    Hi all

    I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

    Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

    The SELECT statement in question retrieves a single row from a table containing 10 columns.

    Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

    I know about stored proc return values and about output parameters, but I think I am looking for something different.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You can create a table variable and return it to the outer proc .... i assume thats what you are looking for

    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    instead of procedure create a table-value udf

  4. #4
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34
    If SQLServer 2000, use the @table datatype
    If SQLServer 7.0, create a temp table in proc 1 and populate it in proc 2 (just be aware of that proc 2 is gonna recompile each time its run)
    - Jonte

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If it's Always just 1 row (are you sure) wht not pass back OUTPUT variables?
    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.

  6. #6
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by Jonte
    If SQLServer 2000, use the @table datatype
    If SQLServer 7.0, create a temp table in proc 1 and populate it in proc 2 (just be aware of that proc 2 is gonna recompile each time its run)
    Thanks to Enigma, ms_sql_dba and Jonte. A table variable is exactly what I was searching for.

  7. #7
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by Brett Kaiser
    If it's Always just 1 row (are you sure) wht not pass back OUTPUT variables?
    Hi

    I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

    However, I understand that output variables are generally better for performance that using a table variable?

  8. #8
    Join Date
    Sep 2003
    Posts
    39
    Originally posted by mattkrevs
    Hi

    I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

    However, I understand that output variables are generally better for performance that using a table variable?
    Well, obviously, if you had a table variable with 15 columns or you had 15 output parameters, then the 15 output parameters would have less overhead.

    Oh, and don't use UDF's when you can use SP's. UDF don't get precompiled.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by mattkrevs
    Hi

    I was looking for a solution where I didnt have to declare 10+ output variables, or one that would work on occassions where there is more than 1 row returned

    However, I understand that output variables are generally better for performance that using a table variable?
    I like lazy....

    But it seems misguided...you still have to code the local table variables anyway...

    And in what instances will you get more than 1 row...

    what would you do wiuth them....sounds like you need to make a decision anyway...why not do it in the sproc?
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by beyond cool
    Oh, and don't use UDF's when you can use SP's. UDF don't get precompiled.
    Can you explain in more detail what you mean by that?

  11. #11
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by Brett Kaiser
    I like lazy....

    But it seems misguided...you still have to code the local table variables anyway...

    And in what instances will you get more than 1 row...

    what would you do wiuth them....sounds like you need to make a decision anyway...why not do it in the sproc?
    Hmmm. You know me too well. Yes its a bit lazy I guess. The stored proc I want to create and call checks for the existance of a row in a table and creates it if it doesnt find it.
    Since the calling stored procs only needs info on 2 of the columns of the created row at present then I guess output params might be the best option.

    I am interested in the table variable as it is a little more flexible and future proof as I can make all of the columns in the created row available to all calling stored procs.

  12. #12
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by mattkrevs
    Hmmm. You know me too well. Yes its a bit lazy I guess. The stored proc I want to create and call checks for the existance of a row in a table and creates it if it doesnt find it.
    Since the calling stored procs only needs info on 2 of the columns of the created row at present then I guess output params might be the best option.

    I am interested in the table variable as it is a little more flexible and future proof as I can make all of the columns in the created row available to all calling stored procs.
    Brett - I think I'll go with your output parameters option. I found 2 problems with using table variables

    1. I didnt realise you had to fully declare table variables including the table structure. Kind of defeats the purpose of 'lazy' coding

    2. I tried this and I got the error "EXECUTE cannot be used as a source when inserting into a table variable." so it looks like it doesnt work anyway. I found other posters had the same problem and they ended up using temp tables instead.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, don't get me wrong....lazy is a good thing...

    It means you're trying to find more effecient ways to do something, rather than the long way around...

    Can you posted what you tried though? I'm curious as to why it didn't work for you...
    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.

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I kinda like the concept of lazy coding.
    Its fun and its challenging!!!!

    I believe the laziest i've encountered has to be this one
    Last edited by Enigma; 01-21-04 at 10:11.
    Get yourself a copy of the The Holy Book

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

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Good site, Enigma

Posting Permissions

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