How to Set dynamic column name or Change column name dynamicly

How to Change column name dynamically
Declare @.Column_name Varchar(30)
Set @.Column_name = (Select Name from Customer where ...)
Create table #temp
(
Name Varchar(30)
Date datetime
Sales Money
)
EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
It failsThis only seems to work on physical tables, i.e. not temporary ones.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||M wrote:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
Posting guidelines: http://vyaskn.tripod.com/posting.htm
The code you posted will work if you are in the context of tempdb (where the
temp table is located). You may be able to execute the change from the
context of another database using dynamic SQL (see EXEC and sp_executesql in
BOL for more information)
David Gugick
Quest Software|||Don't do that.
You are trying to mix data with metadata, which is a definite No-No
Roji. P. Thomas
http://toponewithties.blogspot.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||No one else asked this: why? Why so you think you need to change schema on
the fly?
ML
http://milambda.blogspot.com/|||I want a stored procudure to return a recordset which column name set on the
fly.
Declare @.Column_name1 Varchar(30)
Declare @.Column_name2 Varchar(30)
Set @.Column_name1 = (Select Name from Customer where ...)
Set @.Column_name2 = (Select Name from Customer where ...)
Create #Temp (
@.Column_name1 Varchar(30) -- Set @.Column_name1 as Column name as column
namename
@.Column_name2 Varchar(30) --will not work but I want actual customer
[Date] datetime
)
While
Begin
Select ... into #Temp from .. where
...
End
Select * from #Temp
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DFC9E35F-FEB7-4451-B78F-DF84D2B28951@.microsoft.com...
> No one else asked this: why? Why so you think you need to change schema on
> the fly?
>
> ML
> --
> http://milambda.blogspot.com/
>|||Holy guacamole! Have you ever read anything in Books Online? Read through
this, please:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
I see no reason for the temporary table. Never use "select *" in production
code!
You need to explicitly list all columns you want in the result-set and asign
an appropriate ALIAS to set specific column names. In your specific case thi
s
is best done on the client, since you'd have to use dynamic SQL on the
server. Anyway, here goes...
You'd need something like this:
declare @.statement nvarchar(4000)
declare @.Col1Name sysname
declare @.Col2Name sysname
set @.Col1Name = 'Jim'
set @.Col2Name = 'Bob'
set @.statement = 'select <col1> as ' + @.Col1Name + '
,<col2> as ' + @.Col2Name + '
from <table>
where <conditions>'
Which constitutes:
select <col1> as Jim
,<col2> as Bob
from <table>
where <conditions>
You then have to execute the statement using EXEC:
exec (@.statement)
ML
http://milambda.blogspot.com/|||M (mxchen@.hotvoice.com) writes:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
Apart from the very dubious in this, this works:
Declare @.Column_name Varchar(30)
Set @.Column_name = 'nisse'
Create table #temp
(
Name Varchar(30),
Date datetime,
Sales Money
)
EXEC tempdb..sp_rename '#temp.Name', @.Column_name, 'COLUMN'
select *from #temp
You need to add tempdb.. to set the context for sp_rename.
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