Update if NOT NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update if NOT NULL

Author

Topic

marly
Starting Member

6 Posts

Posted - 2010-03-01 : 13:57:20

I have a MS-SQL Server 2008 that I have a small little database. The database is full of questions/answers.
The database structure is like this;
QuestionID (Primary Key)
Question
Answer
difficultyID
categoryID
usedID

I have a stored procedure that I have a select statement that I randomly select based on difficultyID, categoryID and usedID.
The usedID is there because I don’t want to repeat randomly selected questions (as I only have 15 questions). What I did was add a field called usedID and if it’s 0 it hasn’t been selected and if it’s 1 then it has.
When the stored procedure is run, it randomly selects the question and updates that question to another table and sets the usedID from 0 to 1. This works perfectly fine until it goes through all 15 questions and then it updates the other table with a “NULL” value, (obviously because my original select statement is going to return null since there are no more questions to meet my query).

What I would like to do, is on the on the select statement, do something like, “If it’s null”, then insert XYZ.
Here’s my Stored Procedure:

ALTER PROCEDURE [dbo].[sp_QuestionAnswer]
@CategoryID int,
@DifficultyID int

AS
BEGIN

-- Randomly select the question/answer and then insert it into the Projector Table.
-- Here I would like to update the dbo.showprojector with the select here, otherwise I want it to do something else if it returns NULL.

UPDATE dbo.ShowProjector
SET questionID = (SELECT TOP 1 ID FROM dbo.questions WHERE CategoryID=@CategoryID AND DifficultyID=@DifficultyID AND UsedID=0 ORDER BY NEWID());

-- Find out which question we randomly selected, grab the ID and return the values.

DECLARE @QuestionID as int;
SET @QuestionID = (SELECT questionID FROM dbo.ShowProjector);
SELECT * from dbo.questions WHERE ID = @QuestionID;

-- Set UsesdID to 1 to set the question as used.
IF (@QuestionID IS NOT Null ) BEGIN
UPDATE dbo.questions
SET UsedID = 1 WHERE ID = @QuestionID;
END
-- Set ShowAnswer to 0 as not to show the answer on the projector.
UPDATE dbo.ShowProjector
SET showanswer=0 WHERE questionID = @QuestionID;

END

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-01 : 20:48:21

What you have will only ever work for a single user once, and your approach seems very fragile and complex but I may have misunderstood what you are trying to do. Can you explain more in terms of requirements rather than implementation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 09:53:47

can you tell what exactly is your require? is your intention to update dbo.ShowProjector with random values of ID FROM dbo.questions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources