ADD COLUMN USING ALTER TABLE 1

[CanNeverThinkOfaName]

DROP TABLE #temp
CREATE TABLE #temp ([User] nvarchar(100))
INSERT INTO #temp
SELECT [User]
FROM tblStatsUserDetailed
GROUP BY [User]

DECLARE @StatsDate nvarchar(10)
SET @StatsDate = 'SQL'

ALTER TABLE #temp ADD @StatsDate nvarchar(10) null

I'm basically trying to add the column name 'SQL' to the table using a variable.

It won't accept using a variable in the syntax
The reason I'm doing it this way is I will be cursoring throught a SQL Statement and using the result (eg 23/03/2005) and making that the column name so I will need to use a variable in the ALTER statement (So the table will now say User as Col1 and 23/03/2005 as Col2 etc)


is it possible please?

thanks

 

[Katy44]

You may be able to use the exec statement to do this:

exec('ALTER TABLE #temp ADD''' + convert(varchar,@StatsDate) + ''' nvarchar(10) null')

I'm not sure about the single quotes, but you get the idea.
I don't even know if this will work, but it's worth a try

 

[CanNeverThinkOfaName]

I eventually figured it out

this is the stored procedure

DROP TABLE #temp
CREATE TABLE #temp ([User] nvarchar(100))
INSERT INTO #temp
SELECT [User]
FROM tblStatsUserDetailed
GROUP BY [User]

DECLARE PivotCursor CURSOR FOR
SELECT StatsDate
FROM qryBillingStatsPivot
GROUP BY StatsDate
ORDER BY StatsDate DESC

DECLARE @Alter nvarchar(4000)
DECLARE @StatsDate smalldatetime
SELECT @Alter = ''

OPEN PivotCursor
FETCH NEXT FROM PivotCursor INTO @StatsDate
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Alter = 'ALTER TABLE #temp ADD ' + ' "' + convert(nvarchar(10),@StatsDate,103) + '"' + ' VARCHAR(10) '

SELECT @Alter = LEFT(@Alter,LEN(@Alter))

EXEC(@Alter)

FETCH NEXT FROM PivotCursor INTO @StatsDate

END
CLOSE PivotCursor


thanks for the help!

 

[ESquared]

Dear mariagriffin,

Column names shouldn't have data in them. Here, you are creating column names that are dates. Instead of making your table continually wider this way, you should make it taller. Create a single column called 'WhateverDate' and then insert a record for each new value you would have put in the new date-specific column.

When you're all done, if you need to display the results in a "wide" format, you can use a pivot-type query which we can show you how to do.

I guarantee, though, that you're going to run into serious and difficult headaches with the design you're using.