Creating temp table using parameter.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating temp table using parameter.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-03 : 08:06:09

mohanes writes "Hi,

Im currently using SQL 2000 with windows xp. Im wondering if it is possible to create a temp table where i use a parameter to create the column name? Let me explain my situation. basically im creating a dynamic sql where i have to pick up some value in the database table and use that value to create a temp table.
for example:

@ColName --being the column name that i have picked up from a tbl
Create Table #Temp
(
Index INT IDENTITY (1,1),
@ColName VARCHAR(50)
)

Can i create a temp table column using a parameter? If yes can you please explain what are the possible ways?

Thank You.

Regards,
Mohanes"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-03 : 08:46:00

you need to write the dynamic sql for creating ur table ..

Declare @QryString varchar(8000)
Declare @ColName Varchar(100)

Set @ColName = 'Tmp'

Set @QryString = 'Create Table Tmp ( ' + @ColName + ' Varchar(100))'
Exec (@QryString )

Select * from Tmp

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 08:46:12

you have to use Dynamic SQL to do this.

But why do you have to specifically named a temp table's column name ? Wouldn't it be easier to use a fixed column name rather than depending on another table.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 08:48:04

Only using dynamic sql.
As a temp table is dropped at the end of the batch that created it that would mean placing all the code in dynamic sql.
Another option is to creat4e the table then add the column using dynamic sql. This would mean all the following code would have to be in dynamic sql but can be separate batches.

why do you care what the column in the temp table is called?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources