Creating a dynamic temporary table

I need to dynamically create a temporary table like this (I don't know its
structure until runtime - it is based on selections made by user):
DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(250)
+ ', ' + @.col2 + 'varchar2(250))'
EXEC(@.CreateStatement)
--following is code to fill this table
The table created is not accessible after the line EXEC(@.CreateStatement). I
know this (temp table have a scope limited to the stored procedure that
created them).
Is there another way to accomplish this? I also tried using table variables,
but I wasn't able to make a stored procedure that returns a table variable.YOU CAN USE FUNCTION INSTEAD OF STORED PROC
CREATE FUNCTION TEMP (@.col1 varchar(250) ,@.col2 varchar(250))
RETURNS TABLE
as
RETURN SELECT @.col1+','+@.col2 as TEXT
"razdanro" wrote:
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 + 'varchar(25
0)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table variable
s,
> but I wasn't able to make a stored procedure that returns a table variable.[/color
]|||You can create tempdb..temptable or create ##temptable and it will be
available until it is dropped or the sql server is re-booted.. The
difference between #temptable and ##temptable
#temptable is a non-sharable connection specific temporary table. It goes
away when the SP (if created in an sp) or connection goes away..
##temptable can be seen by all spids, and lives until you drop it or the
server is re-booted... if you are wiriting for mutliple concurrnet users,
you may have to check for its existence before creating it... or come up
with some unque name, and/or attach a spid to separate your rows from those
inserted by another spid.
hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||create the table first using a hard coded "create table" statement (with at
least one column - a dummy column if needed) - then dynamically alter its
structure.
"razdanro" <razdanro@.discussions.microsoft.com> wrote in message
news:8F211B2B-42BE-4608-98EB-C4E350D64D8C@.microsoft.com...
> I need to dynamically create a temporary table like this (I don't know its
> structure until runtime - it is based on selections made by user):
> DECLARE @.CreateStatement = 'CREATE TABLE #tmpTable (' + @.co1 +
'varchar(250)
> + ', ' + @.col2 + 'varchar2(250))'
> EXEC(@.CreateStatement)
> --following is code to fill this table
> The table created is not accessible after the line EXEC(@.CreateStatement).
I
> know this (temp table have a scope limited to the stored procedure that
> created them).
> Is there another way to accomplish this? I also tried using table
variables,
> but I wasn't able to make a stored procedure that returns a table
variable.|||Do not write SQL this way.
Temporary tables tell us that you are really writing procedural code
and have not learned to think in sets and declarative code yet. A temp
table is a "scratch tape" for an algorithm based on procedural steps in
95% of the cases. You probably should be using derived tables or
VIEWs.
Dynamic SQL tell us that you do not know what you are doing, so you
have to let a random stranger create a table in your data model at the
last minute.
Using over-sized VARCHAR(n) values tells us that you did no research to
find the proper size, but just grabbed a large dummy value. This also
means that you have no data model and probalby no data dictionary.
Finally, you will never learn SQL this way. You have already decided
on HOW you want to solve a problem. So people will show you how to
write kludges for your bad solution. But if you had posted WHAT you
want to do, then you might get a relational answer.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Let's try again with the actual problem.|||Here is the data model that I didn't design, but I have to work with right n
ow.
I have three entities: Companies, Sites and Contacts. A Company has 0 or
more Sites, a Site has 0 or more Contacts.
The application must allow users to add properties of these entities
dynamically.
These Properties are held in a table, and the values allowed are in a
PropertyValues table. There is also a table EntityProperty which is an
intersection table between Entities and PropertyValues.
So I have a design that actually stores data and metadata.
And now I have to make a SQLBuilder based on these Entities. I don't know
what Property will be selected as output, that's why I need to create a
dynamic temporary table to return the result.