Problem with ALTER TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with ALTER TABLE

Author

Topic

vestrinang
Starting Member

6 Posts

Posted - 2004-09-13 : 05:44:58

I have this body of a function:

DECLARE MYVALUE NVARCHAR
DECLARE @MYTABLE TABLE(ONE UNIQUEIDENTIFIER, TWO NVARCHAR)
DECLARE MYCURSOR CURSOR FOR SELECT THREE FROM EXISTING_TABLE
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @MYVALUE
WHILE @@FETCH_STATUS=0
BEGIN
/* here it says:
Incorrect syntax near @MYTABLE
*/
ALTER TABLE @MYTABLE ADD @MYVALUE NVARCHAR
FETCH NEXT FROM MYCURSOR INTO @MYVALUE
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

Practically, I want to add columns to my table, whose names are stored in another (persistent) table.

What am I missing ?

Thanks a lot !

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 05:51:58

see comments in red

quote:
Originally posted by vestrinang

I have this body of a function:

DECLARE @MYVALUE NVARCHAR(size)
DECLARE @MYTABLE TABLE(ONE UNIQUEIDENTIFIER, TWO NVARCHAR(size))
DECLARE MYCURSOR CURSOR FOR SELECT THREE FROM EXISTING_TABLE
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @MYVALUE
WHILE @@FETCH_STATUS=0
BEGIN
/* here it says:
Incorrect syntax near @MYTABLE
*/
ALTER TABLE @MYTABLE ADD @MYVALUE (NVARCHAR(size))
FETCH NEXT FROM MYCURSOR INTO @MYVALUE
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

Practically, I want to add columns to my table, whose names are stored in another (persistent) table.

What am I missing ?

Thanks a lot !

vestrinang
Starting Member

6 Posts

Posted - 2004-09-13 : 06:08:27

Thanks alot for answering so fast !
I made the changes, but it won't work. It says the same thing.

Any other ideas ?

Thanks again !

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 06:50:57

sorry...remove @ from @myvalue and @mytable
ALTER TABLE @MYTABLE ADD MYVALUE (NVARCHAR(size))

or it seems you're trying to alter the table based on the variables?

vestrinang
Starting Member

6 Posts

Posted - 2004-09-13 : 06:52:34

I also tried

1. Removed DECLARE @MYTABLE TABLE...
2. DECLARE @MYDEF NVARCHAR(100)
3. SET @MYDEF='ONE UNIQUEIDENTIFIER,TWO NVARCHAR(50)'
4. instead of ALTER TABLE i wrote SET @MYDEF=@MYDEF+','+@MYVALUE+' NVARCHAR(50)'
5. After the loop i wrote DECLARE @MYTABLE TABLE(@MYDEF)
And still doesn't work
I thought I could do a lot of work on the server, instead of
writing code on the client :(

Still, I cannot accept the idea that I can't create a table whose column names are stored as rows (values) in a table, in T-SQL.
Any kind of TRANSFORM here ?

vestrinang
Starting Member

6 Posts

Posted - 2004-09-13 : 07:00:24

Yes, I want that the name of my new column to be the value stored in @MYVALUE.
@MYTABLE is a variable of type table. Shouldn't it have an @ in front of it ?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-13 : 07:39:29

You can't use a variable in the ALTER TABLE statement unless you use dynamic SQL.

You would need something like this:

DECLARE @sql NVARCHAR(4000), @table NVARCHAR(256)

SELECT @table = 'table_name'

SELECT @sql = 'ALTER TABLE ' + @table + ' ADD COLUMN column_name NVARCHAR(55)'

EXEC sp_executesql @sql

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 07:58:37

use dynamic sql to pass the value of the parameters.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 08:00:40

lolz should have closed the reply window first.

quote:
Originally posted by derrickleggett

You can't use a variable in the ALTER TABLE statement unless you use dynamic SQL.

You would need something like this:

DECLARE @sql NVARCHAR(4000), @table NVARCHAR(256)

SELECT @table = 'table_name'

SELECT @sql = 'ALTER TABLE ' + @table + ' ADD COLUMN column_name NVARCHAR(55)'

EXEC sp_executesql @sql

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vestrinang
Starting Member

6 Posts

Posted - 2004-09-13 : 08:15:19

Yes, I figured the dynamic part, but as long as @MYTABLE is a local var of type table (the table doesn't exist in the database, nor is it a temporary one, right ?), how can I expect sp_executesql to do its job ?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 23:44:48

so let me clarify:

you have two tables, table1 and table2

1. table1 is already created as table variable
2. table2 has a field which contains the column name and column definitions
3. you wish to traverse table2, select the column data and apply it to the alter table command so that you'll have:

example: table2 has column fieldnames with values 'field1 nvarchar', 'field2 nvarchar'
and you want
alter table table1 add column field1 nvarchar
alter table table1 add column field2 nvarchar

is this what you want?

DECLARE @MYVALUE nvarchar(100)
declare @sql nvarchar(500)
declare @existing_table table(three nvarchar(100))
create table #mytable (ONE UNIQUEIDENTIFIER, TWO NVARCHAR)
insert into @existing_table(three) values ('field1 nvarchar')
insert into @existing_table (three) values ('field2 nvarchar')

DECLARE MYCURSOR CURSOR FOR SELECT THREE FROM @EXISTING_TABLE
OPEN MYCURSOR

FETCH NEXT FROM MYCURSOR INTO @MYVALUE

WHILE @@FETCH_STATUS=0
BEGIN
set @sql= 'Alter table #mytable add ' + @myvalue
exec sp_executesql @sql
FETCH NEXT FROM MYCURSOR INTO @MYVALUE
END

select * from #mytable

CLOSE MYCURSOR
DEALLOCATE MYCURSOR
drop table #mytable

vestrinang
Starting Member

6 Posts

Posted - 2004-09-16 : 01:14:37

Thanks alot for your help.
I didn't want to create any persistent table and alter it (as it would be if I wanted to use sp_executesql), but it seems it is no other way. I made an adjustment to the database and used sq_executesql, as you wrote above, and solved my problem.

Again, thanks again for your help !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources