SQL Apprentice Question
This is my poll table:
CREATE TABLE [dbo].[Poll](
[Id] [int] NOT NULL,
[Statement] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Answer1] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Score1] [int] NULL,
[Answer2] [nvarchar](500) COLLATE Latin1_General_CI_AI NULL,
[Score2] [int] NULL
I have a statement with two answers. If users select answer1 the score1
value has to be updated with + 1.
The stored procedure to update the score will get a '1' (for answer1) or a
'2' (for answer2) (and Id).
The stored procedure should update the Score1 or Score2 column. But how do I
know the correct column name?
And second, I first have to select the current score of that column.
Maybe, I'm thinking the wrong way. But I don't know how to do this.
Thanks for your help,
Let's fix the DDL first. All those nulls made no sense, you had no
CREATE TABLE Poll
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt NVARCHAR(500) NOT NULL,
answer1 NVARCHAR(500) NOT NULL,
score1 INTEGER DEFAULT 0 NOT NULL
CHECK (score1 >= 0),
answer2 NVARCHAR(500) NOT NULL,
score2 INTEGER DEFAULT 0 NOT NULL
CHECK (score2 >= 0));
CREATE PROCEDURE UpdatePollScores
(@my_question_nbr INTEGER, @my_answer_nbr INTEGER)
= score1 + CASE @my_answer_nbr WHEN 1 THEN 1 ELSE 0 END,
= score2 + CASE @my_answer_nbr WHEN 2 THEN 2 ELSE 0 END
WHERE question_nbr = @my_question_nbr;