Does dynamic SQL allow table variables?

9 Feb 2006 8:28 PM

Test Test

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.developersdex.com ***

9 Feb 2006 8:36 PM

Aaron Bertrand [SQL Server MVP]

The problem is that you didn't create the #Dept table in the same scope.
Inside the EXEC() there is no #Dept table.

Show quoteHide quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

9 Feb 2006 8:50 PM

MJKulangara

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

9 Feb 2006 8:51 PM

Mark Williams

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.

--

Show quoteHide quote

"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.developersdex.com ***
>

9 Feb 2006 10:03 PM

Jens

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..

9 Feb 2006 11:18 PM

Erland Sommarskog

Test Test (farooqhs_2***@yahoo.com) writes:
Show quoteHide quote

> 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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Feb 2006 9:01 PM

Test Test

Thanks to everyone!!!

*** Sent via Developersdex http://www.developersdex.com ***