Alter Table 2

[cfriedberg]

I want to dynamically create columns in a table.

This is a sample of the code that I want to use:

Declare @ColName varchar(15)
Set @ColName = 'NEW'
Alter Table ProjAct
Add @ColName varchar(15)

The problem appears to be that SQL 7.0 does not like the fact that I have used the variable @ColName to specify the name of the new column. I eventually want to use variables for the column type as well.

In the world of FoxPro there is an operator "&" which is used for Macro Substitution making a variable appear as a literal.

Does such a thing exist in SQL or is there any other remedy for the problem?

 

[xcata]

Hi,
You may try dynamic sql:

Declare @ColName varchar(15),@sSql varchar(255)
Set @ColName = 'NEW'
set @sSql = 'Alter Table ProjAct Add ' +
@ColName + ' varchar(15)'
exec(@sSql)

 

[RickCole]

This should do the trick for you


Declare @ColName varchar(15)
Set @ColName = 'NEW'

exec ('Alter Table Table ProjAct
Add ' +@ColName + ' varchar (15)')


Rick Cole