stored procedure to insert into a table variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stored procedure to insert into a table variable?

Author

Topic

cedartie
Starting Member

6 Posts

Posted - 2005-01-21 : 10:14:53

Is it possible to use a stored procedure to insert into a table variable in layman's terms here is what I'm trying to do...

Here is the first Proc
******************************************
Create Procedure dbo.sp_Test

as

select 'Now','Then','Later'

******************************************
Later call the first Proc in the second and populate a table
******************************************
Create Procedure dbo.sp_Test2

as

declare @T table(when1 varchar(50),when2 varchar(50),when3 varchar(50));

Insert Into @T
Values(exec sp_Test)

Select *
from @T
***************************************

Thanks in advance for any help offered

Marty

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-21 : 10:46:23

you can't use execute with table variables. use temp table instead.

Go with the flow & have fun! Else fight the flow

ericms
Starting Member

1 Post

Posted - 2005-01-24 : 01:28:26

When inserting the table, try the following:

Insert Into @T
exec sp_Test

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 03:33:02

ericms:

you can't do that. that doesn't work with table variables:

this code gives error:
EXECUTE cannot be used as a source when inserting into a table variable.


create proc sp_Test
as
select 1, 'value 1' union all
select 2, 'value 2' union all
select 3, 'value 3' union all
select 4, 'value 4' union all
select 5, 'value 5'

declare @t table (number int, name varchar(10))

Insert Into @t
exec sp_Test

drop proc sp_Test

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-24 : 08:35:58

Nothing wrong with using a temp table here.

I'm starting to think/wonder that Microsoft put something like "temp tables cause AIDS, hemmorhoids, and BO" in Books Online somwhere.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-24 : 08:36:28

Daniel said they did.

Damian

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 08:47:25

who's daniel?

Go with the flow & have fun! Else fight the flow

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-24 : 09:14:41

He was an SQL expert

Damian

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 09:16:15

was??

or was he a highly paid consultant??

Go with the flow & have fun! Else fight the flow

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-24 : 15:24:40

He was a regular here years ago. He had some *interesting* "oponions", and liked to argue them a lot.

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-24 : 19:37:05

He was a self-admitted brain dump MCDBA, didn't know his ass from his elbow. He spent quite a bit of time and (badly misspelled) effort defending brain dump sites, and his own "interesting oponions" about SQL Server, even when they fully, demonstrably contradicted Books Online and all common sense.

Those were fun days....NOT.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 19:42:09

I wasn't around back then, but I went through his posts a while back and got a good laugh:

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=2428

Tara

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-24 : 20:13:45

Horay.

You know it is Access machine.
He/She is the stupid.

Cohort

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources