October 29, 2009 at 3:35 pm
I am working on a survey application which contains 12 questions and each question contains 6 options
so I made two tables in the database one with 2 columns (question Id,question)
and one with 4 columns (Answer id,Answer,question Id,votes)
I stored all the 72 options in the answers table.
Every time when the user completes the survey and clicks the submit button i want to increase the no.of.votes by 1 based on user selection.
I thought of writing a stored procedure like this which i got line and it is gud for only one survey question
ALTER PROCEDURE dbo.IncrementVote
(
@i_OptionId INT
)
AS
-- GETS THE CURRENT NUMBER OF VOTES FOR THAT OPTION
DECLARE @i_NumberOfVoteS INT
SELECT @i_NumberOfVotes = Votes
FROM PollOptions
WHERE PK_OptionId = @i_OptionId
UPDATE PollOptions
SET Votes = (@i_NumberOfVotes + 1)
WHERE PK_OptionId = @i_OptionId
RETURN
I have 12 questions like dat.
So need some help in how to write i dont have much of exp on stored procedures.
October 29, 2009 at 3:47 pm
Hi
I'm not sure if I got you.
You can call the procedure with any answer-id and it should work fine.
Either there is a problem with your design or it works. 😛
Just a slight shortage for your procedure. You don't have to select the value first to update it.
ALTER PROCEDURE dbo.IncrementVote
(
@i_OptionId INT
)
AS
UPDATE PollOptions
SET Votes = Votes + 1
WHERE PK_OptionId = @i_OptionId
RETURN
Greets
Flo
October 29, 2009 at 4:18 pm
nag4.net
How is the data being passed from your application to the procedure, is it a comma delimited string ... or? And how to handle that is your problem? For as
Florian Reischl has shown your procedure works
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply