Dynamic generating a SQL statement that includes "INTO #temp"?

Hi there. I started with a simple stored procedure like
select col1, col2 into #temp from table1
select * from #temp
...Do something else here...
For some reason, I'd like to generate the first query dynamically.
declare @.sqlStmt varchar(200)
set @.sqlStmt = 'select col1, col2 into #temp from table1'
exec (@.sqlStmt)
select * from #temp
Unfortunately, it seems like #temp table is not visible anymore in the
"select * from #temp"
- Is it because exec creates a new session, and so #temp created in
the new session is not visible in the original session?
- Using global temp table ##temp may be too dangerous if two
concurrent users are running the same stored procedure. Right?
- Another workaround is to include the "select * from #temp" into the
@.sqlStmt. However, it is not a very elegant solution if the size of
stored procedure is big, but the portion that I'd like to generate
dynamically is small.
- Any other better solution?
ThanksInstead of using the SELECT...INTO statement, create a fixed temporary table
or a table variable and insert data into it. I can't really see any
legitimate reasons to use SELECT...INTO in your case (based on your post).
ML
http://milambda.blogspot.com/|||Thanks! That helps!|||>> Any other better solution? <<
First, avoid dynamic SQL. This says that you have no idea what you
wanted the procedure to do, so you have to "fake it" at the last
minute.
Next, from your narative it looks like a derived table or a VIEW is a
better answer, instead of mimicking the way we wrote scratch tapes in
1950's tape file systems.
CREATE VIEW Foobar (col1, col2)
AS
SELECT col1, col2 FROM Table1;
and then in the stored procedure, which will be compiled in the
database and not built dynamically. The VIEW will always be current.
CREATE PROCEDURE Woowoo (..)
BEGIN ..
SELECT col1, col2 FROM Foobar;
.Do something else here..
END;|||--CELKO-- (jcelko212@.earthlink.net) writes:
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
Rubbish. To see an example of how dynamic SQL can be used to solve a
common business problem, see http://www.sommarskog.se/dyn-search.html.
(And in difference to your books, this is a free resource. :-)
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|||To answer you questions:
When you exec (@.SQL), exec sp_executesql, or exec MyStoredProcedure, any
local temporary tables (#MyTable) will go out of scope and be automatically
deleted when the respective statement ends.
You can get around this by using a global temporary table (##MyTable) but as
you stated, a global temporary table is visable to all processes. Not only
does the same stored procedure executed concurrently have problems but ANY
T-SQL located in other stored procedure, dynamically submiteed by
applications, etc. that happen to create/use a global temporary table of the
same name (##MyTable) will have problems too.
SQL Server protects local temporary tables by concatenating a system
generated numeric suffix to the name. You could do the same. Seeing you're
dynamically creating the global table, you could concatenate the @.@.SPID
(process id) or something else. But then the remaining code would have to b
e
dynamic too and either exec (@.SQL) or exec master.dbo.sp_executesql (which
has more functionality)
Sounds like you need a permanent user table in your database (as suggested
by another forum member) and have a column that contains your @.@.SPID to
idenify the block of rows that belong to your process as opposed to other
concurrent executions.
Col1 - PK, int, identity, clustered (data is always added at the end of the
table data)
Col2 - SPID
Col3-n logical keys of your data
unique constraint on col2, col3, etc.
for rerunability, delete from xxx where spid = @.@.SPID so you have a fresh
workspace
if you need several workspaces, add another column after SPID that creates a
sub-block of data. you're sp can have as many sub-blocks as needed.
Just my two cents,
Joe
"domtam@.hotmail.com" wrote:

> Hi there. I started with a simple stored procedure like
> select col1, col2 into #temp from table1
> select * from #temp
> ....Do something else here...
> For some reason, I'd like to generate the first query dynamically.
> declare @.sqlStmt varchar(200)
> set @.sqlStmt = 'select col1, col2 into #temp from table1'
> exec (@.sqlStmt)
> select * from #temp
> Unfortunately, it seems like #temp table is not visible anymore in the
> "select * from #temp"
> - Is it because exec creates a new session, and so #temp created in
> the new session is not visible in the original session?
> - Using global temp table ##temp may be too dangerous if two
> concurrent users are running the same stored procedure. Right?
> - Another workaround is to include the "select * from #temp" into the
> @.sqlStmt. However, it is not a very elegant solution if the size of
> stored procedure is big, but the portion that I'd like to generate
> dynamically is small.
> - Any other better solution?
> Thanks
>|||> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
First, ignore Celko.
Because he sticks to the ANSI SQL standard he probably hasn't used these
Microsoft SQL Server features so he just blanks them and says they are
kludges etc...
Celko needs to get out and do some real development work and get some real
development experience.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134003996.366060.171490@.z14g2000cwz.googlegroups.com...
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
> Next, from your narative it looks like a derived table or a VIEW is a
> better answer, instead of mimicking the way we wrote scratch tapes in
> 1950's tape file systems.
> CREATE VIEW Foobar (col1, col2)
> AS
> SELECT col1, col2 FROM Table1;
> and then in the stored procedure, which will be compiled in the
> database and not built dynamically. The VIEW will always be current.
> CREATE PROCEDURE Woowoo (..)
> BEGIN ..
> SELECT col1, col2 FROM Foobar;
> ..Do something else here..
> END;
>