If the field for answer is in the same table, you need to change SELECT statement:
SELECT @counter=COALESCE(ID,0)
FROM Quiz
WHERE Id = @randno AND HasBeenAlreadyAnswered = False
You should randomize untill
@counter=0
.
In pseudo code:
1) generate random number (set
@random
)
2) check if question with
ID = @random
has been already answered
a) if yes, goto 1)
b) if not, exit while loop and return record
Below SP should works for you:
DECLARE @id INT
DECLARE @random INT
SET @id =0
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
WHILE (SELECT COALESCE(ID,0) FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered=True)=0
BEGIN
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
SELECT @id = ID FROM Quiz WHERE ID = @random
END
SELECT *
FROM Quiz
WHERE ID = @id AND HasBeenAlreadyAnswered=False
That's all ;)
More about:
WHILE[
^]
[EDIT #1]
After your explanation, i see that your database design is bad. It's not possible to generate random question, if you do not store information about user answers... This is necessary to eliminate duplicates inside "random algorithm".
See examples:
Create a Quiz (for kids)[
^]
Online Quiz[
^]
Quiz - C# and .NET Secrets[
^]
Online Exam in C# ASP NET[
^]
At this moment solution sounds like: You need to redesign database.