sp_executesql and table variables

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)

sp_executesql and table variables

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2004-08-27 : 13:49:44

Is there a scope issue with using sp_executesql to INSERT records into a table variable?

I keep getting a "Must declare the variable '@myTableVariable'."

Should I resort to using a temporary table instead?

BCrowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 13:55:29

@myTableVariable stays defined in a single batch. so you shoud probably use #temp table

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 13:58:07

this will probably answer your question more:
create table #table (blah int)
insert #table select 1 union all select 2
select * from #table
go
select * from #table
drop table #table

declare @table table (blah int)
insert @table select 1 union all select 2
select * from @table
go
select * from @table

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-27 : 14:04:00

It'd be easier if we saw the code, but the execute is it's own thread...

do an sp_who2 active and you'll see the spid that it spawns

But I guess you could do


DECLARE @sql varchar(8000), @myDate99 varchar(25)
SELECT @myDate99 = '1/1/1997'
SELECT @sql = 
  ' DECLARE @x table(Col1 int)'
+ ' INSERT INTO @x(Col1) SELECT MAX(OrderId) FROM Orders WHERE OrderDate > ' + '''' + @myDate99 + ''''
+ ' SELECT * FROM  @x '

EXEC(@sql)

But it's best to avoid dynamic....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources