Alter Table Problem

Question

Post reply

Alter Table Problem

ajosh k john

Valued Member

Points: 69
More actions
November 21, 2005 at 10:39 pm

#90026

Hi,
Can any body tell how can alter a table by passing a parameter.
I have a Table wit one field and I have an itration based on a comma separeted sting.my code is like this.

create table kpuser.tbl(Vtpe varchar(50))
declare @UnitID varchar(8000),@FieldName varchar(50),@Unit_ID varchar(30)
set @UnitID=252,253,254,255,
Declare @Flag int
  declare @delimiter nchar(1)--
set @delimiter = ','
set @Flag=0
      DECLARE @pos int
     DECLARE @tmpval varchar(8000),@last varchar(50)
      SET @pos = charindex(@delimiter, @UnitID)
print @pos
WHILE @pos > 0
     BEGIN
print 'sdsds'
print @pos
SET @tmpval = left(@UnitID, charindex(@delimiter, @UnitID) - 1)
SET @Unit_ID= @tmpval
select @FieldName=Unit_Name from tbl_Unit_Master where Unit_ID=@Unit_ID
  alter table kpuser.tbl add @pos varchar(50)
SET @UnitID = substring(@UnitID, @pos + 1, len(@UnitID))
SET @pos = charindex(@delimiter, @UnitID)
end

I got the error like this
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '@pos'.
Can any body help me to sort out this ...
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

What is this: alter table kpuser.tbl add @pos varchar(50) ?

Probably you want just insert value?

INSERT INTO kpuser.tbl

SELECT @pos

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 2

I'm not sure, but I'm afraid he is trying to disassemble a long string and create a table structure on the fly, naming the added columns based on some part of the original concatenated text. However, it is hard to discern from the posted piece of SQL what it really should do, since there are several syntax and/or typing errors in it... so maybe you're right after all, Sergiy.

David Burrows SSC Guru Points: 65144 More actions · Answer 3

My guess would be

create table kpuser.tbl(Vtpe varchar(50))

declare @UnitID varchar(8000),@FieldName varchar(50),@Unit_ID varchar(30)

set @UnitID = '252,253,254,255,'

Declare @Flag int

declare @delimiter nchar(1)

set @delimiter = ','

set @Flag=0

DECLARE @pos int

DECLARE @tmpval varchar(8000),@last varchar(50)

SET @pos = charindex(@delimiter, @UnitID)

WHILE @pos > 0

BEGIN

SET @tmpval = left(@UnitID, charindex(@delimiter, @UnitID) - 1)

SET @Unit_ID= @tmpval

SELECT @FieldName=Unit_Name from tbl_Unit_Master where Unit_id=@Unit_id

EXEC('alter table kpuser.tbl add ' + @FieldName + ' varchar(50)')

SET @UnitID = substring(@UnitID, @pos + 1, len(@UnitID))

SET @pos = charindex(@delimiter, @UnitID)

END

Far away is close at hand in the images of elsewhere.
Anon.

ajosh k john Valued Member Points: 69 More actions · Answer 4

ajosh k john

Valued Member

Points: 69

November 23, 2005 at 9:32 pm

#605613

hi,

Thanks a lot