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

dynamic column name?

I want to create a table where the column names and number of columns is not
known. It can be a temp table or table variable.
Something like:
DECLARE @.output table (@.columnname int)
or
ALTER TABLE @.output ADD @.columnname varchar(50)
-Max*shudder*
http://www.sommarskog.se/dynamic_sql.html
http://www.aspfaq.com/
(Reverse address to reply.)
"Max" <nospam@.notvalid.com> wrote in message
news:u8i9m9qHFHA.588@.TK2MSFTNGP15.phx.gbl...
> I want to create a table where the column names and number of columns is
not
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>|||>> I want to create a table where the column names and number of
columns is not known.<<
Why, in the name of God!!' The whole idea of a database is that you
have a reality, you model it and then you work with it.
What you are saying is tha tyou have no idea what you are doing.
Tell me the name of your employer. I think that I can get $2000.00 per
day consulting contract fixing your screw ups. My estimate is based on
a mere 35 years in the IT industry, and 10 years on the ANSI X3H2
Database Standards Committee.
Max, if this is no joke and you are writing databases, then stop and
walk away from your job. Your question is that kind of fundamentally
stupid.
And before you start whining about myn abusing you, consider that last
year I did an email consult with an organization that sent medical
supplies to Africa. Their problem was a programmer who did not know
what 1NF was. The shipments were shorted and children died.
It take SIX YEARS to become a Journeyman Union Carpenter in New York
State. how many years did you think that you need to say are database
designer?|||I want to output a table that is dynamically created in a stored proc.
Queries are dynamically generated all the time. Why is that stupid?
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
> columns is not known.<<
> Why, in the name of God!!' The whole idea of a database is that you
> have a reality, you model it and then you work with it.|||>> I want to output a table that is dynamically created in a stored
proc. Queries are dynamically generated all the time. Why is that
stupid? <<
All of 40 years of software engineering, you idiot :) Do you want to
the references ?
--CELKO--|||From someone who gets strange ideas all the time:
Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell in
this Sacred and Holy place with your blasphemies ideas. You will cause the
Holy Judgment and wrath of the great and powerful gods of SQL (and there a
lot of’em around here) to rain destruction upon us all!!! Your doom will
be
complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!
I mean really! These religious fanatics around here really get carried away
sometimes. So someone has an idea. It may be good one, it may be a bad one.
No need to get all puritanical. Come on! Lighten up already. Believe it or
not our souls do not hang by a thread.
and as always i wish to say that i get a LOT of good advice here all the tim
e!
thank all
kes
"Max" wrote:

> I want to create a table where the column names and number of columns is n
ot
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>
>|||What is so fundamentally wrong with discovering and creating database schema
or metadata during runtime? I can give you 50 enterprise level examples of
this.
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109722606.650060.311970@.f14g2000cwb.googlegroups.com...
> proc. Queries are dynamically generated all the time. Why is that
> stupid? <<
> All of 40 years of software engineering, you idiot :) Do you want to
> the references ?
> --CELKO--
>|||I've always imagined how I'd be at age 60, a grumpy old man, except for that
I would have embraced high technology instead of condemned it as would the
typical grumpy old man of today. Well I think I just found an example of
this hi-tech old man. ;)
-Max
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
> From someone who gets strange ideas all the time:
> Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell
> in
> this Sacred and Holy place with your blasphemies ideas. You will cause the
> Holy Judgment and wrath of the great and powerful gods of SQL (and there a
> lot of'em around here) to rain destruction upon us all!!! Your doom will
> be
> complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!|||Why are the return columns of the query changing? Send the columns back and
let the presentation tier determine which columns to show/hide. Or have
different stored procedures based on the type of report.
On 3/1/05 6:40 PM, in article OACwYgrHFHA.720@.TK2MSFTNGP14.phx.gbl, "Max"
<nospam@.notvalid.com> wrote:

> I want to output a table that is dynamically created in a stored proc.
> Queries are dynamically generated all the time. Why is that stupid?
> -Max
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
>|||Ye hath seen the LIGHT!! Go now!! and sin no more!!!!
(ya gota love this ______. have a good one!!!) ;)
"Max" wrote:

> I've always imagined how I'd be at age 60, a grumpy old man, except for th
at
> I would have embraced high technology instead of condemned it as would the
> typical grumpy old man of today. Well I think I just found an example of
> this hi-tech old man. ;)
> -Max
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
>
>