ALTER TABLE To Add multiple Columns with string variable

Question

For my requirement, I am creating one temporary table like below:

CREATE TABLE #ResourceMonthlyTimeReport
(
    RowId INT IDENTITY(1,1),
    ResourceID UNIQUEIDENTIFIER,
    TaskId UNIQUEIDENTIFIER,
    ProjectId UNIQUEIDENTIFIER,
    -- Some column names containing date should come here.
)

In the above table, date columns should display like below:

[01 Mar, 2013] NVARCHAR(10),
[02 Mar, 2013] NVARCHAR(10),
[03 Mar, 2013] NVARCHAR(10),

... and the number of columns depends on the parameter values @FromDate and @ToDate

I have written a function dbo.F_ST_DaysColumns(@FromDate, @ToDate) that generates the following output as string:

[01 Mar, 2013] NVARCHAR(10),
[02 Mar, 2013] NVARCHAR(10),
[03 Mar, 2013] NVARCHAR(10)

Now, I am not understanding how to append this result to the above temporary table #ResourceMonthlyTimeReport.

I have done something like this:

DECLARE @DateColumns AS VARCHAR(MAX)
SET @DateColumns = dbo.F_ST_DaysColumns(@FromDate, @ToDate)

ALTER TABLE #ResourceMonthlyTimeReport ADD @DateColumns

But, showing error like below:

Msg 102, Level 15, State 1, Procedure ST_Proc_Rpt_MonthlyTimeReportSummary, Line 94
Incorrect syntax near '@DateColumns'

Ashok kumar · Accepted Answer · 2013-04-04 05:09:11Z

1

Oh... it is too simple, but I couldn't think like this before I posted this question in forum. Anyway, somebody else can use it:

EXEC('ALTER TABLE #ResourceMonthlyTimeReport ADD ' + @DateColumns)

answered Apr 4, 2013 at 5:09

Ashok kumar

1,6205 gold badges36 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thejustv · Accepted Answer · 2015-04-11 10:07:15Z

0

Table Variable is like a temporary table created in the memory which can be used within the scope where it is being created. Once it is created it is not possible to alter the structure of the Table Variable by using ALTER TABLE statement. It is better to add another column in the declaration and use it whenever you want. Note that Table Variables can have columns which accepts NULL values also.

answered Apr 11, 2015 at 10:07

thejustv

2,02527 silver badges43 bronze badges

Collectives™ on Stack Overflow

ALTER TABLE To Add multiple Columns with string variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related