Very Computer

Hi,

I have a stored procedure called, sp_A, and within this I want to call
another stored procedure, sp_B, where sp_B returns x number of rows of data.

How do I access the data that is returned from sp_B within sp_A?

Can anyone help?

Huge Thanks
Jacqueline Mills

1.
Create temp table in sp_A which matches ur op of sp_B. then
insert #temp
exec sp_B

U can then access #temp.

2. Create temp table in sp_A
Use the temp table in sp_B (insert temp table in sp_b) and make a call
to sp_b in sp_a as follows
...
create table #temp (...)
....
....
exec sp_B

select * from #temp
...
...

Hope this helps!

I have some information about this on:
http://www.algonet.se/~sommar/share_data.html.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

This is the prefered method.

Quote:>2. Create temp table in sp_A
>Use the temp table in sp_B (insert temp table in sp_b) and make a call
>to sp_b in sp_a as follows
>create table #temp (...)
>exec sp_B
>select * from #temp

All non-global temporary tables (as far as I know) are destroyed after
the stored procedure executes, FYI. You must use a global temporary
table in order for this procedure to work.

Global temporary tables present their own problems (they are shareable
over login sessions I believe, and they are only destroyed when the
creator logs out -- correct me if I'm wrong! -- meaning you have to
use random appendages in a multi-user environment)... so I strongly
would recommend using the first method.

The following code does not work:

create procedure a as
begin
        create table #temp (c varchar(1))
        insert into #temp (c) values('a')
end
create procedure b as
begin
        exec a
        select * from #temp
end

b

(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Procedure b, Line 5
Invalid object name '#temp'.

The following code works:
create procedure a as
begin
        create table ##temp (c varchar(1))
        insert into ##temp (c) values('a')
end

create procedure b as
begin
        exec a
        select * from ##temp
end

b
Output: a

But if you try and execute it without dropping ##temp:

b
Server: Msg 2714, Level 16, State 6, Procedure a, Line 4
There is already an object named '##temp' in the database.

This method has a couple of weaknesses:

1) INSERT EXEC cannot be applied recursively. That is if sp_B tries
   to user INSERT EXEC itself, this fails.
2) If someone changes the result of sp_B to fit a client, then sp_A.
   Thus, this method is not robust.
3) sp_B will execute in the context of a transaction. This may or
   may not been issue, depening on isolation level, what it does etc.

Quote:>>2. Create temp table in sp_A
>>Use the temp table in sp_B (insert temp table in sp_b) and make a call
>>to sp_b in sp_a as follows
>>create table #temp (...)
>>exec sp_B
>>select * from #temp

> All non-global temporary tables (as far as I know) are destroyed after
> the stored procedure executes, FYI. You must use a global temporary
> table in order for this procedure to work.

Nope. You create the table in the caller, and you refer to it in the
callee:

   CREATE PROCEDURE a_SP AS
     CREATE TABLE #tmp (a int NOT NULL)
     EXEC b_SP
     SELECT a FROM #tmp
  go
  CREATE TABLE #tmp(a int NOT NULL)
  go
  CREATE PROCEDURE b_SP AS
     INSERT #tmp (a) VALUES (4711)
  go
  DROP TABLE #tmp
  go
  EXEC a_SP

On SQL2000 and SQL7 it is not necessary to have the CREATE/DROP of the
temp table outside the procedure, since you have deferred name resolution.
However, if the table exists when the procedure is created, SQL Server
will trap any incorrect column references.

This method has my strong recommendation.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

On Tue, 5 Nov 2002 22:21:33 +0000 (UTC), Erland Sommarskog

Oh, OK. That didn't occur to me, but this works too.

The weakness of this method is that now, b_SP becomes dependant on
either another stored procedure to create the temporary table, or
executing statements in your script. There is no way you can now get
seperate resultsets merely from executing b_SP. (Global temporary
tables as I outlined in the previous post, have the same problem: they
will require some sort of wrapper in the executing code or stored
procedure to maintain dropping and creating the tables.) This creates
a disadvantage in some scenerios - for instance, if b_SP is already
used heavily in query/report calls, it might be a pain to modify
everything that uses it to accomadate a_SP.

So I see the only way you can keep the stored procedures independent
of each other, really, is _insert into #temp exec(a_SP)_. That way,
a_SP will remain independant of b_SP, you can get resultsets from
either one. However, the problems you pointed out are all valid. In
particular, the lack of recursiveness and the non-robustness can pose
problems down the road. It's the solution I prefer if I have to do a
SP exec, simply because I cannot think of a scenerio where I'd want to
use the results of b_SP, where b_SP isn't already being used
somewhere. It is an assumption I made though based on my experience...

Which leads another possible solution: incorporate the code of b_SP
into a_SP directly, forget all this messing around with temp tables
and execs - just copy and paste code. It leads to maintenance problems
(if b_SP is wrong, you have 2 SPs to change) but allows for more
optimized, streamlined code in the end.

Hmm... design food for thought I suppose. :)

There are a couple of ways to address this. Recently when I was a bit
lazy, I wrote in the b_SP of the occasion:

  IF object_id('tempdb..#temptbl') IS NULL
  BEGIN
     $INCLUDE temptbl.sqlinc
  END

Where temptbl.sqlinc includes the declaration of the temp table. Now,
I reckon that not everyone has the access to a preprocessor for T-SQL
(but the above preprocessor is available in the public domain, see
my post about AbaPerls earlier this evening).

But a stricter arrangement would be to push the logic of b_sp to
b2_sp, and a_sp will call b2_sp. The b_sp as you know it will only
create the temp table, call b2_sp, and then return the result set
from the temp table.

As long as you don't have a preprocessor, you still have to maintain
two CREATE TABLE statements (or more, if there are more callers to
b2_sp), but this is in no way different than when you use INSERT..EXEC.
In fact it is somewhat more forgiving, because if you add a column that
d_sp needs, and forget to change a_sp, a_sp may still survive. With
INSERT EXEC, a_sp dies instantly.

(A side note: in the preprocessor I have, you must list in the include-
file which files that references the include-file, so it is very easy
to get hold of all referencing files and changes these.)

Quote:> So I see the only way you can keep the stored procedures independent
> of each other, really, is _insert into #temp exec(a_SP)_.

No, they are not independent, since if you change the result set of
the callee, you MUST change the caller. It has never happened to you
that an INSERT EXEC have failed because of this?

Quote:> I cannot think of a scenerio where I'd want to use the results of b_SP,
> where b_SP isn't already being used somewhere. It is an assumption I
> made though based on my experience...

Well, one reason for this is simply that you are working with some
complex stored procedure, and you find that you want to move some
complex logic to a sub-procedure, and you need to share a temp table
between the two.

Quote:> Which leads another possible solution: incorporate the code of b_SP
> into a_SP directly, forget all this messing around with temp tables
> and execs - just copy and paste code. It leads to maintenance problems
> (if b_SP is wrong, you have 2 SPs to change) but allows for more
> optimized, streamlined code in the end.

If b_SP is a simple SELECT statement, then this path is probably the
way to go. But if b_SP is 200 lines of code, you are asked to be
whacked on the fingers if you do this.

Another possibility which have not discussed is to turn b_SP (or its
core logic) into a table-valued user-defined function.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

On Wed, 6 Nov 2002 22:27:05 +0000 (UTC), Erland Sommarskog

Not really (I actually rarely use stored procedures within stored
procedures.) :)

The use of a stored procedure to create the temporary table in the
existing stored procedure, then renaming the existing stored
procedure, is a bit of a hack trick, but to me its a good one, as it
addresses quite a number of the issues described here.

Quote:>> I cannot think of a scenerio where I'd want to use the results of b_SP,
>> where b_SP isn't already being used somewhere. It is an assumption I
>> made though based on my experience...
>Well, one reason for this is simply that you are working with some
>complex stored procedure, and you find that you want to move some
>complex logic to a sub-procedure, and you need to share a temp table
>between the two.

I don't know that you'd want to do that by design. SQL isn't C++,
after all - this idea doesn't sound very optimized to me. If you
already heavily dependant on temporary tables in your query, probably
there isn't a big performance hit - but I believe a sensible goal in
query design is to not use temporary tables whenever possible. The
possibility where you turn b_SP into a table variable SQL function (if
you have SQL Server 2000) gets around this, I imagine.

Quote:>> Which leads another possible solution: incorporate the code of b_SP
>> into a_SP directly, forget all this messing around with temp tables
>> and execs - just copy and paste code. It leads to maintenance problems
>> (if b_SP is wrong, you have 2 SPs to change) but allows for more
>> optimized, streamlined code in the end.
>If b_SP is a simple SELECT statement, then this path is probably the
>way to go. But if b_SP is 200 lines of code, you are asked to be
>whacked on the fingers if you do this.

Very true. It's a possibility though to consider though. :)

In several of cases where I have done this, the procedure being reused
in this way, already had a temp table and the procedure closed with a
SELECT from this temp table.

It is true that with use derived tables, you can in many cases evade
use of temp tables altogether. Then again, using a temp table as a
work area where you add things as you move on from table to table to
get data, gives simpler programming, and thus less risk for errors
and more simple to maintain. And the performance cost is usually not
that big.

I mentioned the number 200 lines in my previous post. Many of the
procedures in our system are longer than this.

Besides, derived tables were added in SQL 6.5, and our system is far
older than this.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?