DialM4Monkey
asked on
use ALTER TABLE on a Table Variable
Hey folks,
Im stumped. I have a table variable that I need to alter dynamically:
DELCARE @tDate smalldatetime
DECLARE @BeginDate smalldatetime
DECLARE @EndDate smalldatetime
SET @BeginDate = '1/1/03'
SET @EndDate = '12/31/03'
SET @tDate = @Begindate
DECLARE @Scores TABLE (StudentName varchar(100))
WHILE (@tDate < @EndDate) BEGIN
SET @tVar = dbo.MonthName(Month(@tDate ),1) + ' ' + convert(varchar,year(@tDat e))
ALTER TABLE @Scores ADD @tVar VARCHAR(20) NULL
SET @tDate = DATEADD(month,1,@tDate)
END
If the answer is you cant use ALTER TABLE for Table Variables, then how can I change my table variable after its been declared
I also tried DECLARE @Scores TABLE (@fieldNames) and that didn't work at all.
HELP!!
Im stumped. I have a table variable that I need to alter dynamically:
DELCARE @tDate smalldatetime
DECLARE @BeginDate smalldatetime
DECLARE @EndDate smalldatetime
SET @BeginDate = '1/1/03'
SET @EndDate = '12/31/03'
SET @tDate = @Begindate
DECLARE @Scores TABLE (StudentName varchar(100))
WHILE (@tDate < @EndDate) BEGIN
SET @tVar = dbo.MonthName(Month(@tDate
ALTER TABLE @Scores ADD @tVar VARCHAR(20) NULL
SET @tDate = DATEADD(month,1,@tDate)
END
If the answer is you cant use ALTER TABLE for Table Variables, then how can I change my table variable after its been declared
I also tried DECLARE @Scores TABLE (@fieldNames) and that didn't work at all.
HELP!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create table x(id smalldatetime constraint t_id default getdate())
and
declare @t table (id smalldatetime constraint t_id default getdate())
Use temporary tables instead like namasi pointed out. Just don't forget to remove them once you're finished.