June 11, 2009 at 8:25 am
like
declare @tbl table(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));
declare @var nvarchar(max);
set @var='mynk'
alter table @tbl
add column + @var + nvarchar(max);
select * from @tbl
June 11, 2009 at 8:29 am
June 11, 2009 at 8:39 am
I cant I have also tried same with temporary table but i can not
create table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));
declare @var nvarchar(max);
set @var='mynk'
alter table #tbl
add column + @var + nvarchar(max);
select * from #tbl
June 11, 2009 at 8:48 am
The problem is with the variable, you can't use it that way. You should define the whole alter statement in a string variable and the execute it:
DECLARE @sql nvarchar(4000)
SET @sql = 'alter table #tbl add ' + @var + ' nvarchar(max); '
EXEC sp_executesql @sql
EDITED: removed 'column' keyword. (Damn cut&paste!)
-- Gianluca Sartori
June 11, 2009 at 8:50 am
mjarsaniya (6/11/2009)
I cant I have also tried same with temporary table but i can notcreate table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));
declare @var nvarchar(max);
set @var='mynk'
alter table #tbl
add column + @var + nvarchar(max);
select * from #tbl
It's not the temp table that's the problem there. It's that you can't specify a column name in a variable, plus the keyword 'column' doesn't need to be there.
create table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));
alter table #tbl
add mynk nvarchar(max);
select * from #tbl
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2009 at 8:55 am
GilaMonster (6/11/2009)
... the keyword 'column' doesn't need to be there.
That's right Gail, I'll edit my previous post.
Mjarsaniya, you can use the dynamic sql when the columnname is determined at runtime or passed as parameter, otherwise go as Gail suggested.
-- Gianluca Sartori
June 12, 2009 at 12:44 am
Ok.thanks My work is done.
thanks to you and Gail.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy