Into cursor problem

My code in SP
DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable '
print @.sql
EXEC @.sql
I got the error Invalid object name '#temptable' , Does anyone know the
reason ? thanks a lotUnless #temptable is declared within the container SP, it only exists within
the scope of the dynamic sql and will be destroyed when it completes:
DECLARE @.sql nvarchar(4000)
CREATE TABLE #temptable ...
SELECT @.sql = 'INSERT #tempTable SELECT ... from #myTable'
print @.sql
EXEC @.sql
Mr Tea
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23HgFUx7GFHA.3352@.TK2MSFTNGP10.phx.gbl...
> My code in SP
> DECLARE @.sql nvarchar(4000)
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable
> '
> print @.sql
> EXEC @.sql
> I got the error Invalid object name '#temptable' , Does anyone know the
> reason ? thanks a lot
>|||Try this
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
#myTable '
print @.sql
EXEC (''+@.sql +'')
Madhivanan|||> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
?
Does that will anyhow solve the problem?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109396430.717724.313510@.z14g2000cwz.googlegroups.com...
> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
> Madhivanan
>|||Thomas
I tested that in the query analyser. The text displayed is (9 row(s)
affected). But actually the table was not created. Can you tell me
where the prblem is?
Madhivanan|||Madhivanan,

> I tested that in the query analyser
Tested what? The code you posted?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109401722.488986.74000@.o13g2000cwo.googlegroups.com...
> Thomas
> I tested that in the query analyser. The text displayed is (9 row(s)
> affected). But actually the table was not created. Can you tell me
> where the prblem is?
> Madhivanan
>|||Yes thomas
Madhivanan|||The code you posted will not work on its own.
(It was even missing the declare statement )
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109402883.603761.286740@.f14g2000cwb.googlegroups.com...
> Yes thomas
> Madhivanan
>|||Thomas,
Run this and see the result
create table #temp (no int, name varchar(100))
insert into #temp values(1,'Name1')
insert into #temp values(2,'Name2')
insert into #temp values(3,'Name3')
insert into #temp values(4,'Name4')
insert into #temp values(5,'Name5')
Declare @.sql varchar(100)
SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
print @.sql
EXEC (''+@.sql +'')
drop table #temp
Actually it is running without any error. But the table #tempTable is
not created.
Can you find out the reason?
Madhivanan|||Madhivanan,
The #tempTable is not visible because EXEC() has its own scope.
The following are the limitations of EXEC
a.. Within the SQL batch you cannot access local variables or parameters
of the calling stored procedure.
b.. Any USE statement will not affect the calling stored procedure.
c.. Temp tables created in the SQL batch will not be available to the
calling procedure since they are dropped when the block exits - just like
when you exit a stored procedure. The batch can however access tables
created in the calling procedure.
d.. If you issue a SET command in the dynamic SQL batch, the effect of the
SET command lasts for the duration of the dynamic SQL batch only.
e.. The query plan for the batch is not part of the plan for the calling
procedure. Cachewise the query is just as good as a bare SQL statement sent
from the client.
f.. If the batch results in an condition that leads to abortion of the
batch, for instance rollback in a trigger, not only the batch of dynamic SQL
is terminated, but also the calling procedure (and its caller and so on).
Its taken from this excellent article by Erland Sommarskog.
http://www.sommarskog.se/dynamic_sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109413962.850048.149990@.z14g2000cwz.googlegroups.com...
> Thomas,
> Run this and see the result
>
> create table #temp (no int, name varchar(100))
> insert into #temp values(1,'Name1')
> insert into #temp values(2,'Name2')
> insert into #temp values(3,'Name3')
> insert into #temp values(4,'Name4')
> insert into #temp values(5,'Name5')
> Declare @.sql varchar(100)
> SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
> print @.sql
> EXEC (''+@.sql +'')
> drop table #temp
> Actually it is running without any error. But the table #tempTable is
> not created.
> Can you find out the reason?
> Madhivanan
>