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.

ThanksYou can create a table variable and return it to the outer proc ... i assume thats what you are looking for

:)|||instead of procedure create a table-value udf|||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)|||If it's Always just 1 row (are you sure) wht not pass back OUTPUT variables?|||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.|||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?|||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.|||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?|||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?|||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.|||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.|||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...|||I kinda like the concept of lazy (http://www.lazydba.com/lazydba_info.html) coding.
Its fun and its challenging!!!!

I believe the laziest i've encountered has to be this one (http://www.dbforums.com/showthread.php?threadid=975995)|||Good site, Enigma