CODEWITHSUNDEEP
sql-server-2008
mu_sa
mu_sa

Reputation: 2725

Temp table shared between procedures

I have a procedure lets call it a 

caller

procedure which creates a temp table 

#temp1

and exec another procedure lets call that the 

callee

procedure which insert some values into the #temp1 table so that it can be used by the caller procedure. Now the problem is my callee procedure can only be called via caller or some other procedure that creates a #temp1 table and then executes the callee. Is there a way like introducing a 'If' condition checking if the callee has been called by caller then only executes the statement

'insert #temp1 ....'

and other wise avoiding it.

Thanks

Upvotes: 1

Views: 79

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

If callee will always produce a single result set, and it's that result set that should be inserted into #temp1 if its available, then you might consider shifting the INSERT into the outer procedure, using INSERT ... EXEC inside caller:

CREATE procedure caller
as
   --Various things
   create table #temp1 (column1,column2)
   --Other things
   insert into #temp1 (column1,column2) exec callee
   --Work with #temp1

Upvotes: 0

dferidarov
dferidarov

Reputation: 602

You can implement logic in order to know who called the procedure "callee" - Add param to "callee" with default value = NULL (for example), then change only the "caller" procedure by adding this param with a value of 1 (for example). In the "callee" procedure check the value of this param and if it's different than NULL then work with your temp table.

Code sample:

create procedure [callee]
(
@CalledByCaller bit = NULL
)
as 
set nocount on 
If @CalledByCaller =1 
begin 
    SELECT '3'
    --work with temp table
end
SELECT '2'
go

create procedure [caller] 
as
set nocount on 

exec [callee] @CalledByCaller =1 

go

Upvotes: 1

Related Questions