how to use temp table in inside a procedure called?

[Former Member]

Suppose I have a stored procedure sp1 which call another procedure sp2 like:

create proc sp1

as

begin

    CREATE TABLE #table1 (......)

    Exec sp2

end

then I want to use #table1 in sp2, like

create proc sp2

as

begin

....

   delete from #table1

   INSERT INTO #table1

......

end

How to pass #table1 to sp2? Anywhere to do this?

[former_member188958 · Accepted solution]

The trick to setting this up is to create the #temp1 table before creating sp2 to avoid any "object not found" resolution error while the query tree is being created.  The drop the table.

When sp2 is called by sp1, it will automatically re-resolve it's #temp1 entry to the #temp1 created in sp1.

  CREATE TABLE #table1 (......)

go

create proc sp2

as

begin

....

   delete from #table1

   INSERT INTO #table1

......

end

go

drop table #table1
go

create proc sp1

as

begin

    CREATE TABLE #table1 (......)

    Exec sp2

end

[Former Member · Accepted solution]

thanks, got it. Bret. One more question. If sp2 call another sp3, if this #table1 available for sp3 if I doing thing like:

sp1.

  CREATE TABLE #table1 (......)

go

create proc sp3

as

begin

....

   delete from #table1

   INSERT INTO #table1

......

end

create proc sp2

as

begin

....

   delete from #table1

   INSERT INTO #table1

   exec sp3

......

end

go

drop table #table1
go

create proc sp1

as

begin

    CREATE TABLE #table1 (......)

    Exec sp2

end

[former_member188958 · Accepted solution]

Yes.

[jayrijnbergen · Accepted solution]

if you're on 15.7, than use: deferred name resolution

set it to 1 (disabled by default), and no need to create the temp table anymore outside the proc

object resolution will only be performed when the proc is executed, not when it's created