SQL Server : drop column @variable not working

Question

I'm using Microsoft SQL Server and it seems that I cannot get around this issue.

I have a table on which I will have some dynamic and static columns.

Static columns would be name of product, type of product and the dynamic data would be some production data from ongoing months.

At the every beginning of the month I have to drop from the dynamic columns the past month and add a new month to the end of the table

My solution was saving the name of the column into a local variable and then adding it to the alter statement. But this does not work , it keeps giving me a error as under:

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '@month_b'

I will now add the queries

declare @month_t char(15) 
declare @month_b char(15) 
declare @sql char(30)

set @month_b = (SELECT  top 1 name 
                FROM sys.columns 
                WHERE object_id = OBJECT_ID('dbo.ct_test') 
                  AND name != 'TTNR' AND name != 'Family' AND name like '%B%'
                ORDER BY name ASC)

set @month_t = (SELECT top 1 name 
                FROM sys.columns 
                WHERE object_id = OBJECT_ID('dbo.ct_test') 
                  AND name != 'TTNR' AND name != 'Family' AND name like '%T%'
                ORDER BY name ASC)

alter table ct_test 
   drop column @month_b

I cannot find a solution to this, can you help me.

Thank you in advance

This doesn't seem like a good design, btw. Regularly adding and dropping columns is a code smell to me. — Ben Thul, Jan 15 '15 at 11:45

Pரதீப் · Accepted Answer · 2015-01-15T08:22:37.213

5

You need to use Dynamic Query

Declare @sql nvarchar(max)

set @sql = 'alter table ct_test drop column '+ @month_b

Exec sp_executesql @sql

set @sql = 'alter table ct_test drop column '+ @month_t

Exec sp_executesql @sql

edited Jan 15 '15 at 08:22

answered Jan 15 '15 at 08:15

Pரதீப்

91,748
19
131
172

It gives me another error : Msg 214, Level 16, State 2, Procedure sp_execute, Line 1 Procedure expects parameter '@handle' of type 'int'. – hoisu Jan 15 '15 at 08:20
@CaluserIstvan - sorry it should be `sp_executesql` instead of `sp_execute`. Updated check now – Pரதீப் Jan 15 '15 at 08:23

SQL Server : drop column @variable not working

1 Answers1