Luis Suarez Luis Suarez - 2 months ago 6
SQL Question

Use variable as part of the column name in WHILE Loop

I would like to delete multiple columns in a table, how can I use variable as part of the column name in WHILE Loop? Thanks.

DECLARE @colnum INT
Set @colnum = 13
WHILE @colnum <=37
BEGIN

ALTER TABLE PC$
DROP COLUMN F+ @colnum

SET @colnum = @colnum +1
END;

Answer

Use Dynamic SQL.

DECLARE  @colnum INT
Declare @strsql varchar(max)
Set @colnum = 13
WHILE @colnum <=37
BEGIN

set @strsql ='ALTER TABLE PC$
DROP COLUMN F' + cast(@colnum as varchar(20))

exec(@strsql )

SET @colnum = @colnum +1
END;
Comments