Dynamic Column Names

[Waynest]

While using a CREATE TABLE command I would like to use column names which have been determined within the same stored procedure (I want to set column names which represent a w/c date for display in a VB datagrid)

This is the kind of thing I mean...

-- I can determine date values from a calendar table
DECLARE @ColName char(8)
SELECT @ColName = '10/11/03'

DROP TABLE SkuPlan
CREATE TABLE SkuPlan
(
ItemCode char(15),
RecType char(1),
Description varchar(50),
@ColName int
)

This syntax isn't permitted, but any ideas how I could achieve the same effect?

Thanks

 

[LFCfan]

Hi there

look into dynamic sql, and have a read of FAQ183-3132

it would be something like

DECLARE @strSQL varchar(1000)
SET @strSQL = 
'CREATE TABLE SkuPlan (ItemCode char(15),RecType char(1),Description varchar(50), ' + @ColName + 'int)'

EXEC(@strSQL)

hope this helps

 

[psprague]

If you are using special chars in the name, enclose that part of the string with square brackets.

SET @strSQL =
'CREATE TABLE SkuPlan (ItemCode char(15),RecType char(1),Description varchar(50), [' + @ColName + '] int)'