Table vars; what context? - 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

SQL Server Development (2000)

Table vars; what context?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-16 : 22:02:13

I know there was a series of posts earlier about how you apparently cannot pass a table variable to a stored procedure, which kind of sucks.

But I'm also finding that they don't exist even to dynamic SQL run by the procedure that creates them. Try this:


CREATE PROCEDURE p_test AS
set nocount on

DECLARE @tTable Table (x int)
DECLARE @vcQ varchar(200)

insert into @tTable (x) VALUES (1)
insert into @tTable (x) VALUES (2)
insert into @tTable (x) VALUES (3)

select * from @tTable

select @vcQ='select * from @tTable'
exec (@vcQ)

No workie. I'd really like for this to work, but I'd settle for a reasonable explanation of why it doesn't.

Cheers
-b

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-16 : 22:13:03

Because it is a varible rather than a physical table, it will follow the same scoping rules as other variables.

For your example to work, you would need to put the table variable declaration in your dynamic sql as well. Or use a temp table.

Damian

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-16 : 22:16:43

Makes sense. I'll have to use a temp table.

Thanks
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources