Does dynamic SQL allow table variables?

Hello!
Please see below the test code that is using dynamic sql and table
variable. It is not working. I am not sure if the dynamic SQL allows
using table variables?
Thanks!
declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, 'abcd')
insert into #Dept values(2, 'xyz')
set @.s = 'insert into ' + @.Tblvar
' select * from #Dept'
exec (@.s)
select * from @.Tblvar
*** Sent via Developersdex http://www.examnotes.net ***The problem is that you didn't create the #Dept table in the same scope.
Inside the EXEC() there is no #Dept table.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23A2JudbLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||As Aaron says..this is a scope problem...table variables must be used
in the same batch...
exec('declare @.Tblvar TABLE(a int, b varchar(10))
declare @.s varchar(200)
create table #Dept(a int, b varchar(10))
insert into #Dept values(1, ''abcd'')
insert into #Dept values(2, ''xyz'')
insert into @.Tblvar
select * from #Dept
select * from @.Tblvar')
MJKulangara
http://sqladventures.blogspot.com|||I see two problems here. First, you are trying to build your query string,
@.s, by concatenating a string with a table, which won't work. You could
instead to
set @.s = 'insert into @.Tblvar select * from #Dept'
but then you encounter another problem: neither the variable @.Tblvar or the
temporary table #Dept is defined in the scope that the query is executed
under with EXEC.
"Test Test" wrote:

> Hello!
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
>
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes it does, but due to the fact that exec is opening another session
)verything you execute) it has to be put within the context of the (in
your case) INSERT INTO statement. Otherwise you can use a global
temporary data to share data with if you want to.
HTH, Jens Suessmeyer..|||Test Test (farooqhs_2000@.yahoo.com) writes:
> Please see below the test code that is using dynamic sql and table
> variable. It is not working. I am not sure if the dynamic SQL allows
> using table variables?
> Thanks!
> declare @.Tblvar TABLE(a int, b varchar(10))
> declare @.s varchar(200)
> create table #Dept(a int, b varchar(10))
> insert into #Dept values(1, 'abcd')
> insert into #Dept values(2, 'xyz')
> set @.s = 'insert into ' + @.Tblvar
> ' select * from #Dept'
> exec (@.s)
> select * from @.Tblvar
The dynamic SQL constitutes a scope on its own, and variables are
only visible in the direct scope that created it. This is in difference
to temp tables which are visible for inner scopes. (No less than two
posters gave incorrect information on this.)
A scope is a stored procedure, function, trigger - or a batch of dynamic
SQL.
A good demonstration of this is:
CREATE PROCEDURE nestlevel_sp AS
SELECT @.@.nestlevel
EXEC('SELECT @.@.nestlevel')
EXEC sp_executesql N'SELECT @.@.nestlevel'
go
EXEC nestlevel_sp
This prints 1, 2 3 in that order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks to everyone!!!
*** Sent via Developersdex http://www.examnotes.net ***