Table variables - SQL Server Forums

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)

Table variables

Author

Topic

ringworm
Starting Member

6 Posts

Posted - 2007-10-10 : 05:28:17

the below code will not work. Before I use a temp table , will table varables not work in this case?

declare @TableVar table (
col1 varchar(255),
col2 varchar(255),
col3 varchar(255))

Insert Into @TableVar

exec dbo.a3columnresultSet '30 jun 2007','31 jul 2007'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 05:37:50

If you tried, and it didn't work and you sorted out the error message, it will probably not work.

E 12°55'05.25"
N 56°04'39.16"

ringworm
Starting Member

6 Posts

Posted - 2007-10-10 : 05:42:17

sorry i maybe should of made it more clear can i store the results of a SP to a table variable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 05:45:34

If you tried to code above, and understand the error message you get, the possibility that you will have success with the code above is near zero.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-10 : 05:49:18

use a temporary table and not a table variable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 06:50:03

"the possibility that you will have success with the code above is ~~near~~ zero"

I made a test.

I opened Books Online.

I went to "Table Variables" in the index

And it says:

quote:

table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

But I'm not sure if that test is classified as Failure or Success

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 07:02:07

I thought that part were obvious to tell from the error message OP must have gotten

quote:
EXECUTE cannot be used as a source when inserting into a table variable.

However, in SQL Server 2005 it is possible!

declare @t table (i1 varchar(1000), i2 varchar(1000), i3 varchar(1000), i4 varchar(1000), i5 varchar(1000), i6 varchar(1000), i7 varchar(1000), i8 varchar(1000), i9 varchar(1000))

insert @t
exec sp_who

select * from @t

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources