?
Solved

use ALTER TABLE on a Table Variable

Posted on 2003-11-07
4
Medium Priority
?
815 Views
Last Modified: 2012-05-04
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(@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!!
0
Comment
Question by:DialM4Monkey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 1000 total points
ID: 9703225
If you need to dynamicaly add column you will need to use traditional temp tables.

drop table #test

create table #test
(
col1 int
)

alter table #test
add col2 int

select * from #test
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9704079
Just like you cannot place constraints on a table variable.

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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question