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

Question

Hello all!

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. Thanks

Answer 1

IF COLUMNPROPERTY(OBJECT_ID('myTable'), 'ColumnID', 'Question_1') IS NOT NULL
    ALTER TABLE tblFeedback DROP COLUMN Question_1

However, this will drop all rows: you can't have some rows with Answer and some with Question for example.

If I understand you correctly, I would implement as a child table to allow 0 to n question/answer/feedback with a type to define question/answer/feedback etc.

Answer 2

You would need to use dynamic SQL for this and query sys.columns if you really want to change the table structure based on column name every time the feedback form changes. This does seem an unusual requirement as it will delete historic data that you are presumably storing for some reason in the first place

but if you just want to automate something that you would otherwise need to do manually...

Example Syntax

DECLARE @dynsql NVARCHAR(4000)

SELECT @dynsql = ISNULL(@dynsql + ',','') + QUOTENAME(name)
 FROM sys.columns WHERE name LIKE 'Question%'
 AND OBJECT_ID=OBJECT_ID('dbo.tblFeedback')

IF(@@ROWCOUNT > 0)
EXEC ('ALTER TABLE dbo.tblFeedback DROP COLUMN ' + @dynsql)

Answer 3

Please reconsider your DB design. Drop question columns entirely and add tables

Question (QuestionID int identity, Deleted datetime, Text text)  
Answer (FeedbackID int FK to Feedback, QuestionID int FK to Question, Text text)

then use Question.Deleted == null as you criteria wherever to show or hide questions on your form.

Answer 4

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.