DECLARE @strMsg varchar(160), @strTelNo varchar(20), @entrantID int
DECLARE @strDate char(10), @subtemp int, @strSQL varchar(255)
DECLARE @strOutMsg varchar(160), @QHolder varchar(50), @ansCount int, @ansMinID int, @ansMaxID int
DECLARE @AHolder varchar(120), @AHolder1 varchar(120),@AHolder2 varchar(120),@AHolder3 varchar(120),@AHolder4 varchar(120)
DECLARE @intTotalDayQs int, @intEntLatestQ int, @intCheckRabbles int, @intStatus int


SET @strDate = CONVERT(char(10), GETDATE(), 1)
SET @strMsg = 'mabao'
SET @strMsg = RTRIM(LTRIM(LOWER(@strMsg)))
SET @strTelNo = '254722736060'
SET @intCheckRabbles = (SELECT count(*) FROM tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate)

If @strMsg = 'mabao' AND  @intCheckRabbles = 0 -- New Sender, proper keyword
   BEGIN
	INSERT INTO tblEntrants(entCellNo, entMsg, entDateCommitted, entIsQuest, entStatus)
	VALUES (@strTelNo,@strMsg, GetDate(), '1', '1')
	SET @entrantID = (SELECT MAX(entID) FROM tblEntrants WHERE entCellNo =@strTelNo)
	-- Set the next question to ask
	SET @subtemp = (SELECT entQNo from tblEntrants WHERE entID = (@entrantID)) + 1
	--Get corresponding answers for select question for the first timer or repeat correct trial
	SET @ansCount = (SELECT COUNT(*) FROM tblAnswers WHERE (ansDateCommitted = @strDate) AND (ansQID = @subtemp))			
	SET @QHolder = (SELECT qQuestion FROM dbo.tblQuestions WHERE (qNo = @subtemp)AND (qDateCommitted = @strDate))
	-- Set starting point for today's questions (* Questions should never be deleted!)
	SET @ansMinID = (SELECT MIN(ansID) FROM tblAnswers WHERE (ansDateCommitted = @strDate) AND (ansQID = @subtemp))
	-- Prepare the out sms complete with the question and its answers
	SET @AHolder1 = (SELECT  'send ' + ansShortCut + ' for ' + ansAnswer + ', ' FROM tblAnswers WHERE ansDateCommitted = @strDate AND ansID = @ansMinID)
	SET @AHolder2 = (SELECT  ansShortCut + ' for ' + ansAnswer + ', ' FROM tblAnswers WHERE ansDateCommitted = @strDate AND ansID = @ansMinID + 1)
	SET @AHolder3 = (SELECT  ansShortCut + ' for ' + ansAnswer FROM tblAnswers WHERE ansDateCommitted = @strDate AND ansID = @ansMinID + 2)
	SET @AHolder = @AHolder1 + @AHolder2 + @AHolder3
	SET @strOutMsg = @QHolder + ' ' + @AHolder
	-- Prevent qNo field exceeding actual number of questions per day
	SET @intTotalDayQs = (SELECT count(*) from tblQuestions WHERE (qDateCommitted = @strDate))
	SET @intEntLatestQ = (SELECT entQNo from tblEntrants WHERE entID = (SELECT max(entID) from tblEntrants) AND entCellNo = @strTelno AND entDateCommitted >= @strDate)
	If @intEntLatestQ = @intTotalDayQs
		SET NOCOUNT ON
		SET @strOutMsg = (SELECT 'You have attempted all questions for today. Thank you. Try again tomorrow' as OutSMS)				

	If @intEntLatestQ < @intTotalDayQs
		SET NOCOUNT ON
		SET @strOutMsg = (SELECT 'Hi, Let ' + char(39) + 's start...' + @strOutMsg as OutMsg)
		--Update status to 1 to mean the sms has been sent and awaiting response	
		UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp WHERE entID = (@entrantID)
   END
Else 
	If @strMsg = 'mabao' AND  @intCheckRabbles > 0 --already exists, unallowed repetition of mabao keyword
		SET @strOutMsg = (SELECT 'You can only send the word MABAO to 5556 once a day. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556' As OutMsg)
	Else		
		If @strMsg <> 'mabao' AND  @intCheckRabbles = 0 -- first timer for prediction or first timer gibberishing
			If @strMsg = 'predict' -- Belongs to Predict and Win
					SET NOCOUNT ON
					INSERT INTO tblEntrants(entCellNo, entMsg, entDateCommitted, entIsPrediction, entStatus)
					VALUES (@strTelNo,@strMsg, GetDate(), '1', '1')
			Else -- First timer Gibberish officially		   
			   BEGIN
				SET NOCOUNT ON			
				INSERT INTO tblEntrants(entCellNo, entMsg, entDateCommitted, entStatus)
				VALUES (@strTelNo, @strMsg, GetDate(), '2')
				SET @strOutMsg = (SELECT 'Your keyword was not understood. Please sms the word MABAO to 5556' As OutMsg)
				UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		   		
			   END
		Else
			If @strMsg <> 'mabao' AND @intCheckRabbles > 0 -- Either nth timer mabao[1|2|3|4], mabao n, gibberish
				If @strMsg = 'mabao1' or @strMsg = 'mabao2' or @strMsg = 'mabao3' or @strMsg = 'mabao4' --nth timer mabao[1|2|3|4]
					DECLARE @intQNoCheck int, @strCorrectAns char(6)	
					SET @strMsg = lower(replace(@strMsg, ' ' ,'')) -- cleanup	
					-- Check if already dealt with mabao_nth 
					If (SELECT entIsCorrectAns from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate) = 2
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT 'You have already sent your answer for this question. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556'  AS OutMSg)
					Else
						-- Necessary to update entQNo for next question
						SET @subtemp = (SELECT entQNo from tblEntrants WHERE entID = (SELECT MAX(entID) from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 and entDateCommitted >= @strDate)) + 1			
						SET @intQNoCheck = (SELECT entQNo from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 and entDateCommitted >= @strDate )
						SET @strCorrectAns = (SELECT ansShortCut FROM tblAnswers WHERE ansQID = @intQNOCheck AND ansDateCommitted >= @strDate AND ansIsCorrect = 1)
						If @strMsg <> @strCorrectAns
				 		   BEGIN
							SET NOCOUNT ON
							SET @strOutMsg = 'Sorry that' + char(39) + 's not correct. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556'
							SET @strOutMsg = (SELECT @strOutMsg as OutSMS)
							UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;
						   END
						Else -- answer is correct construct appropriate reply
						  BEGIN
							SET NOCOUNT ON
							SET @strOutMsg = 'That' + char(39) + 's correct! you are now entered into weekly draw to win an original Coke soccer ball. Want to try the next question? send MABAO N To 5556'
							SET @strOutMsg = (SELECT @strOutMsg as OutSMS)
							UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		   		
						  END
			Else 
				If @strMsg = 'mabaon' -- N subroutine 
					SET @subtemp = (SELECT entQNo from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate) + 1
					If @intEntLatestQ < @intTotalDayQs
					   BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT @strOutMsg as OutMsg)
						--Update status to 1 to mean the sms has been sent and awaiting response	
						UPDATE tblEntrants SET entStatus = 1, entIscorrectAns = 3, entQNo = @subtemp WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate
					   END
				Else
					SET @strOutMsg = (SELECT 'Your keyword was not understood. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556' As OutMsg)
					UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		 

print @strOutMsg

declare @lo int
If 1= 1
--begin
 set @lo = 1
 print 'hello'
--end
ELSE
 print 'no'

declare @lo int
If 1= 1
begin
 set @lo = 1
 print 'hello'
end
ELSE
 print 'no'

IF @FOO = @BAR
   SET @FOO = @FOO + 1

IF @FOO = @BAR
   SET @FOO = @FOO + 1
   SET @BAR = @BAR + 1

quote:

---

Originally posted by Kristen

"Put a BEGIN - END BLock for Each If Construct which has More than 1 Statement in IF Block"

Our house-style is to ALWAYS put them in - that prevents someone adding a statement in the future and submarining a bug!

i.e. this code is at risk:

IF @FOO = @BAR
   SET @FOO = @FOO + 1

when someone adds:

IF @FOO = @BAR
   SET @FOO = @FOO + 1
   SET @BAR = @BAR + 1

when they only checked the indentation!

Where's that SQL LINT I've been looking for for years?!

Kristen

---

			Else 
				If @strMsg = 'mabaon' -- N subroutine 
					SET @subtemp = (SELECT entQNo from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate) + 1
					If @intEntLatestQ < @intTotalDayQs			
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT @strOutMsg as OutMsg)
						--Update status to 1 to mean the sms has been sent and awaiting response	
						UPDATE tblEntrants SET entStatus = 1, entIscorrectAns = 3, entQNo = @subtemp WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate
					END
					Else
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT 'You have attempted all questions for today. Thank you. Try again tomorrow' as OutSMS)									
					END
				Else
				BEGIN
					SET @strOutMsg = (SELECT 'Your keyword was not understood. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556' As OutMsg)
					UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		 
				END

Else 
BEGIN
				If @strMsg = 'mabaon' -- N subroutine 
                                BEGIN
					SET @subtemp = (SELECT entQNo from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate) + 1
					If @intEntLatestQ < @intTotalDayQs			
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT @strOutMsg as OutMsg)
						--Update status to 1 to mean the sms has been sent and awaiting response	
						UPDATE tblEntrants SET entStatus = 1, entIscorrectAns = 3, entQNo = @subtemp WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate
					END
					Else
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT 'You have attempted all questions for today. Thank you. Try again tomorrow' as OutSMS)									
					END
END
				Else
				BEGIN
					SET @strOutMsg = (SELECT 'Your keyword was not understood. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556' As OutMsg)
					UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		 
				END
END

quote:

---

Originally posted by mr_mist

You still have multiple statements there in a IF that doesn't have a BEGIN END block

You'd need to re-write -

Else 
BEGIN
				If @strMsg = 'mabaon' -- N subroutine 
                                BEGIN
					SET @subtemp = (SELECT entQNo from tblEntrants WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate) + 1
					If @intEntLatestQ < @intTotalDayQs			
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT @strOutMsg as OutMsg)
						--Update status to 1 to mean the sms has been sent and awaiting response	
						UPDATE tblEntrants SET entStatus = 1, entIscorrectAns = 3, entQNo = @subtemp WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate
					END
					Else
					BEGIN
						SET NOCOUNT ON
						SET @strOutMsg = (SELECT 'You have attempted all questions for today. Thank you. Try again tomorrow' as OutSMS)									
					END
END
				Else
				BEGIN
					SET @strOutMsg = (SELECT 'Your keyword was not understood. Want to try the next question for chance to win an original Coke soccer ball? send MABAO N to 5556' As OutMsg)
					UPDATE tblEntrants SET entStatus = 1, entQNo = @subtemp, entIsCorrectAns = 2 WHERE entCellNo = @strTelNo AND entStatus = 1 AND entDateCommitted >= @strDate;		 
				END
END

-------
Moo. :)

---