How do I pass temp table columns to a stored procedure?

Question

I have a stored procedure that calls another stored procedure within it. And I've created a temp table that have the values I want to pass to the inner stored procedure. Is there a way to do this without having to create a bunch of variables?

An example:

CREATE PROCEDURE usr_AdminSaveChanges 
    -- Add the parameters for the stored procedure here
        @blah varchar(5) = NULL,
        @tid int = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- only returns one row
    select testid, testfoo, testbar
    into #testtemp
    from testtable
    where tid = @tid

    exec usr_updateTestSP #testtemp.testid, @blah, #testtemp.testbar
END

Does this answer your question? [How to pass a temp table as a parameter into a separate stored procedure](https://stackoverflow.com/questions/20105443/how-to-pass-a-temp-table-as-a-parameter-into-a-separate-stored-procedure) — Michael Freidgeim, May 20 '22 at 07:21

dean · Answer 1 · 2014-04-25T16:12:28.947

1

Actually, you don't have to do anything, the temp table created inside the calling procedure will automatically be visible from inside the called procedure (but not vice versa). Just make sure you don't have a different temp table with the same name inside it.

See here for more informations and other options:

http://sommarskog.se/share_data.html

Here's a simplified sample:

create procedure proc1
as
create table #t1(i int)
insert #t1 values (1)
exec proc2
go

create procedure proc2
as
select * 
from #t1
go

exec proc1

edited Apr 25 '14 at 16:12

answered Apr 25 '14 at 15:26

dean

9,960
2
25
26

Does my change to the OP still apply to your answer? I guess I'm not seeing how what you suggested works. – dotnetN00b Apr 25 '14 at 16:07
Did you actually try this? – dean Apr 25 '14 at 17:17

Dimt · Answer 2 · 2014-04-25T18:22:22.643

0

You could use a table variable and pass it as a parameter, then you can query that table variable as a regular table in the other SP.

DECLARE @t table(
   testid int,
   testfoo int,
   testbar int
)

select testid, testfoo, testbar
into @t
from testtable
where tid = 2

exec usr_updateTestSP @t

Table Variable can create a high I/O when it operates on a large dataset.

edited Apr 25 '14 at 18:22

answered Apr 25 '14 at 15:26

Dimt

2,278
2
20
26

usr_updateTestSP already exists and is being used elsewhere in my code. So changing the parameters isn't an option. I've made a change to my OP to better reflect the issue. – dotnetN00b Apr 25 '14 at 16:08
@dotnetN00b, well you could pass the table variable along with other parameters you need. Your question was for an option to eliminate other variables. Are you able to modify the called SP ? – Dimt Apr 25 '14 at 18:18

How do I pass temp table columns to a stored procedure?

2 Answers2