Help creating a new column dynamically

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

Transact-SQL (2000)

Help creating a new column dynamically

Author

Topic

RLilley
Starting Member

1 Post

Posted - 2006-10-01 : 10:17:16

I have an existing table and need to create a new column dynamically. The new column name is made up from the current MONTH and YEAR, so if I executed the code today I want to create a column named Oct2006. This also increments the column value by one each time it's executed. The procedure works fine if I hard code an existing column name and remove the variables.

The code I have written is below. using the variable name @monyr in the ADD COLUMN is giving me a syntax error. if someone could help me out I would appreciate it.

DECLARE @mon char(3)
DECLARE @yr char(4)
DECLARE @monyr char(7)
SET @mon = datepart(month,getdate())
SET @yr = datepart(year,getdate())
SET @monyr = @mon+@yr

CREATE PROCEDURE [dbo].[PATCounts] ( @ProdVer char(8))
AS
if not exists (select * from Information_schema.Columns where table_name ='PATUseage' and Column_name=@monyr)
ALTER TABLE PATUseage
ADD @monyr INT NOT NULL default(N'0')

Update PATUseage
Set @monyr = @monyr + 1
Where ProdVer = @Prodver
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-01 : 10:22:30

Sounds like a table design issue. You should have the month value in row instead of column

if possible redesign your table such that the value is in the row. For example


create table PATUseage
(
    datecol      datetime,
    month+value  int
    . . . .
)

barmalej
Starting Member

40 Posts

Posted - 2006-10-01 : 10:49:18

Simply use dynamic SQL for 'ALTER TABLE PATUseage...' if you are not afraid to get some headache to address newly created columns later. Because SELECTs, etc. probably will need the same dynamic SQL if used from stored procedures. For more or less complex database above mentioned headache can turn into a nightmare by the way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources