SQL - Using CASE Statements IN SQL Statements

How To Use IF Statements In Your SET BASED SQL

By Strictly-Software

Now if you are new to SQL you may not know the benefits of the CASE statement. It is basically the way you can implement an IF statement within your SET Based SQL.

I do a lot of work with Horse Racing and I like to rank my horses on a number of variables adding up a "Rating" score by appending values to a default value so that runners with the best score appear at the top.

An example of this would be the simplified cut down version below where I am adding points to a Ratings column that will be displayed in the final results. I used multiple CASE WHEN THEN ELSE END, statements to check various stats a horse may have and if they meet the criteria I append a score to a rolling count e.g

This example uses a couple of CASE statements to show me the horses Last Races Official Rating, but also creates a SCORE using a number of factors which I can then order by easily at the end.

The SQL

SELECT RacePK,RunnerPK,Racedatetime,IsHandicap,CourseName + N' - ' + RaceTime + ' - ' + HorseName + ' @ ' + CurrentOdds as Info,OfficialRating,
		CASE WHEN LastRaceFK>0 THEN (SELECT OfficialRating FROM RACE_RUNNERS WITH (NOLOCK)  WHERE RaceFK=run.LastRaceFK AND HorseNameCountry=run.HorseNameCountry) ELSE 0 END as LastRaceOfficialRating,Class		
		CASE ClassChange WHEN 1 THEN 'UP IN CLASS' WHEN 2 THEN 'DOWN IN CLASS' WHEN 0 THEN 'SAME CLASS' ELSE 'NA' END as ClassChange,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 AND (SELECT Class FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST CLASS INFO' 
			WHEN LastRaceFK > 0 THEN 'LAST RACE CLASS: ' + (SELECT MyClass + ' - ' + RaceName FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) ELSE 'NA' END as LastRaceClass, Going, GoingCode,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 THEN 'LAST RACE GOING: ' + (SELECT UPPER(GOINGCODE) FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK)	
			WHEN LastRaceFK > 0 AND (SELECT GOINGCODE FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST RACE GOING INFO' END as LastRaceGoing,
		LastFinishPos, FORM,  
		Score = CASE WHEN Favourite = 1 THEN 50 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,2) THEN 35
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,3) THEN 20 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,4) THEN 10 
			     ELSE 0 END +
			-- 0 = no change, 1 = up in grade, 2 down in grade, 3 = unknown, -1 default value				
			CASE WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) AND LastFinishPos = 1 THEN 50
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) THEN 45
		             WHEN ClassChange = 1 AND LastFinishPos = 1 AND (HasMaxOfficialRating = 1 OR Favourite = 1) THEN 40
		             WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 35
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 30			     
		             WHEN ClassChange = 1 AND KeyForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 27						 		
			     WHEN ClassChange = 1 AND KeyForm LIKE '%L%' THEN 25
			-- down in grade could have won at this grade before
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos = 1 THEN 35 
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 30
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' THEN 27
			     WHEN ClassChange IN(0,1) THEN 5
			     WHEN ClassChange = 2 THEN 3
			     WHEN ClassChange IN(3,-1) THEN - 5
			     ELSE 0
			     END + 
			-- new IF for no of runs the more win percentage the better 
			CASE WHEN Runs > 5 AND WinPerc = 0 THEN -20   
			     WHEN Runs > 5 AND WinPerc = 100 THEN 100
			     WHEN Runs > 2 AND WinPerc = 100 THEN 50
			     WHEN Runs > 5 AND PlacePerc = 100 THEN 60
			     WHEN Runs > 2 AND PlacePerc = 100 THEN 30
			     WHEN Runs > 5 AND PlacePerc > 70 THEN 50
			     WHEN Runs > 2 AND PlacePerc > 70 THEN 30
			     WHEN Runs > 2 AND LosePerc = 100 THEN -50
			     WHEN Runs > 5 AND LosePerc > 60 THEN -60
			     WHEN Runs = 0 OR LOSEPERC > 70 THEN -50 ELSE -10 END +
			 -- Official Rating higher the better
			CASE WHEN OfficialRating > 170 THEN 300
			     WHEN OfficialRating > 160 THEN 275
			     WHEN OfficialRating > 150 THEN 250
			     WHEN OfficialRating > 125 THEN 200
			     WHEN OfficialRating > 110 THEN 175
			     WHEN OfficialRating > 105 THEN 150
			     WHEN OfficialRating > 105 AND IsHandicap = 1 THEN 100
			     WHEN HasMaxOfficialRating = 1 THEN 50
			     ELSE 10 END
FROM	RACES as ra  with (nolock)
JOIN	RACE_RUNNERS as run with (nolock)  
	ON	ra.RacePK = run.RaceFK 
JOIN	COURSES as c  with (nolock) 
	ON	c.CoursePK = r.CourseFK
WHERE	RacePK = 269330 -- current race I am looking at

The Results

Below are the results from that query outputted in MS SQL Management Studio.

Another Example

This is where I am using a CASE WHEN THEN ELSE END, statement in the ORDER By clause to show the output of a race card. 

The race could be finished and in that case I would want the ORDER of the results to be from no 1 (winner) to the horse that finished last. I would also want any runners not finishing who would get a  finish position of 0 appearing last. 

I would also want any non-runners, horses who were pulled out of the event, before the race to appear at the bottom of the results.

Also with racing, in the flat, horses start the race in stalls, so if it is a flat race I want to show the stall they are in, if the race is a jump or NHF (Bumper or National Hunt Flat Race), then they use no stalls so I would not order by stall no.

The SQL

SELECT	RunnerPK,Racename,raceType,HorseName,Jockey,NonRunner,FinalOdds,CurrentOdds,Draw,Finishposition,favourite,officialrating
FROM	RACE_RUNNERS as run  WITH (nolock)
JOIN	RACES as ra  WITH (nolock)
	ON ra.RacePK=run.RaceFK
WHERE	RacePK=270423
ORDER BY CASE WHEN nonrunner=1 THEN 150
	      WHEN EXISTS(select top(1) runnerPK from race_runners as r1 WITH (nolock) where r1.Racefk=ra.RacePK and finishposition>0) THEN
		   CASE WHEN finishposition=0 then 100 ELSE finishposition END 
	      ELSE CASE when ra.racetype IN('NH','B') THEN horsename else draw END
	 END 

The Results

Below are the results from that query outputted in MS SQL Management Studio.

As I hope you can see the use of a CASE statement either in a SELECT clause or an ORDER BY is very useful for adding an IF statement into a SET based query. 

You can also use them in your JOINS to help decide which table you actually JOIN onto, for example if I had two tables, one for RACES that had not been run yet, and one for RACES that had finished I could add a CASE statement into the JOIN so that if there existed a runner with a finish position or a RACE with a finishing time I would join to the RACE_RESULTS table and for RACES not run yet I would join to the RACE_CARD table (if they were named like that for races before running and after a result was in).

There are actually lots of ways you can use CASE statements in your SQL and it may help you to be able to CONVERT looping code or procedural code with actual IF statements in a ROW by ROW CURSOR or LOOP into a SET BASED SQL statement that will perform a lot quicker.

Please post your favourite CASE statement usages in the comments section.

By Strictly-Software

SQL 2012 Bug - Incorrect Syntax Need BEGIN Statements

SQL 2012 Bug - Incorrect Syntax Need BEGIN Statements

We recently moved Database servers from 2008 to 2012.

Everything seemed to have gone fine until we found that one of our logger database tables had grown to a massive size holding millions of rows and containing many more days records than it should have.

As this was over the Christmas period no-one really noticed until we got back from holiday.However a look in the log files from the MS Agent job showed that the part of the Stored Procedure to delete old records was failing and then the proc was bombing out, which meant none of the older historical records were being deleted.

The error message in the log file was "Incorrect Syntax near begi" and then just a load of failed statements.

There are some Microsoft knowledge base articles about this issue that say the problem is due to:

• The statement contains an IF condition.
• The IF condition does not contain BEGIN and END statements.
• The IF condition is followed by a BEGIN TRY block.
• The IF block is recompiled when you run the query. 

You can read more about the problem on Microsofts own sites:

https://connect.microsoft.com/SQLServer/feedback/details/752276/incorrect-syntax-near-begi-in-sql-2012

and

http://support.microsoft.com/kb/2829372

They actually provide this example code which is supposed to replicate the error in MS SQL 2012 however when I ran it in a query window it worked fine for me.

DECLARE @i INT

IF object_id('tempdb..#temptable') IS NOT NULL
DROP TABLE #temptable

CREATE TABLE #temptable (id INT)
INSERT INTO #temptable VALUES (1),(2),(3);

IF year(getdate())=2012 SELECT @i=(SELECT COUNT(*) AS nr FROM #temptable);
BEGIN TRY
SELECT 'message'
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH

Therefore I then thought that maybe the problem might not be the actual language and queries but something to do with Stored Procedures CONTAINING such language.

The stored procedures that were bombing out were full of statements that had IF statements near BEGIN TRY without an END before it. For example we put in DEBUG statements for logging and being able to debug the code after the midnight hours so we would have code like this all over the place.

DECLARE @i INT

IF @DEBUG = 1
  PRINT 'About to run code to delete old historical records'

BEGIN TRY
  ...code
END TRY
BEGIN CATCH
 .. code
END CATCH

As our DBA didn't want to install a "dodgy" hotfix as he called it, even though SP1 had been installed (which didn't fix the issue) to test this theory out we made sure any of our IF statements were wrapped in BEGIN ..code... END statements like this.

DECLARE @i INT

IF @DEBUG = 1
  BEGIN
       PRINT 'About to run code to delete old historical records'
  END

BEGIN TRY
  ...code
END TRY
BEGIN CATCH
 .. code
END CATCH

So we put the new code into our proc and let it run that night from MS Agent.

Guess what?

When we ran the MS Agent job that night that fired off these stored procedures the whole thing worked!

I don't know if it is necessarily a stored procedure problem or not but this simple fix for our own code seemed to solve the problem without having to apply the hotfix so it seemed to solve the problem for us.

How Microsoft have gotten so far into their roll out of SQL 2012 with such a problem in their code I have no idea but it seems to be such an annoying bug that a lot of people are complaining about it.

Hopefully this solution may fix it all for you as well!