SQL Server, How to DROP COLUMN if condition is satisfied?

Question

I have been trawling google for answers to this but no luck. Any help would be great!

I have a SQL table: tblFeedback. It stores answers to feedback questions. The Questions are held in a different table: tblQuestions. The questions on the feedback form can be changed via a user interface, therefore when the user edits the questions of replaces them, I rewrite the questions to tblQuestions and provide answer columns in tblFeedback (one for each question). Hope thats easy enough to understand.

So at the minute I have 3 questions. My tblFeedback columns look like:
FeedbackID
Name
Date
Question_1
Question_2
Question_3

I want to delete these columns and replace them with new ones when necessary. I am going down the path of using the ALTER TABLE tblFeedback DROP COLUMN ... but I cannot add any criteria using WHERE or anything else.. If I could specify something like 'if column_name starts with Question_%' or 'if column_ID > 3' but if I add WHERE after the COLUMN I get errors.

I am writing this for an asp.net app using c#.

Any help with this would be really really appreciated. Im going for a walk to calm down.

Answer 1

You might be better with an EAV (entity attribute vaue) style database structure for this kind of thing.

create table Question
(
    Id bigint not null primary key identity(1,1),
    Question nvarchar(max) not null
)

create table Feedback
(
    Id bigint not null primary key identity(1,1),
    Date datetime not null,
    Name nvarchar(512) not null,    
)

create table FeedbackAnswers
(
    Id bigint not null primary key identity(1,1),   
    FeedbackId bigint not null,
    QuestionId bigint not null,
    Answer nvarchar(max) not null
)

You will probably need something else to "group" the questions together.

It does make reporting a little more involved though.