sql server: stored procedure into temporary table WITH variables values

Question

I have a temporary table

@MyTmpTable(col1, col2, col3, col4)

and my stored procedure SP1 returns 3 columns values

I can use:

insert into @MyTmpTable (col2, col3, col4) exec SP1

to fill @MyTmpTable but col1 value will always be null, is there any way I can do something like this?

declare @var1 varchar(10) = 'myvalue';
insert into @MyTable(col2, col3, col4) @var1, exec SP1

Can anyone help?

This is not a temporary table. It's a table variable. There are differences. — Zohar Peled
– Zohar Peled, Commented Mar 16, 2017 at 14:38

TheGameiswar · Accepted Answer · 2017-03-16 14:36:56Z

1

if your col1 is always null,you can create it with default value for your requirement to work .see below for demo

create table #test
(
id int default null,
id1 int,
id2 int
)


create proc usp_test
as
begin
select rand()*10,rand()*20
end

insert into #test
(id1,id2)
exec usp_test

answered Mar 16, 2017 at 14:36

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zohar Peled Over a year ago

actually, default null is quite meaningless, I would go with an actual value...

Gordon Bell · Accepted Answer · 2017-03-16 14:55:42Z

0

You could just update col1 after the insert:

update @MyTmpTable set col1 = 'myvalue' where col1 is null;

answered Mar 16, 2017 at 14:55

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

1 Comment

supermyz Over a year ago

That's exactly what I am looking for, thanks! I am not familiar with TSQL, I used PSQL, at this point, I think PSQL is more efficient, I can use SP just like another table, even with joins

Collectives™ on Stack Overflow

sql server: stored procedure into temporary table WITH variables values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related