Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo

SQL Decision Structures


  • Please log in to reply
2 replies to this topic

#1 chili5

chili5

    CC Mentor

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3038 posts
  • Programming Language:Java, C#, PHP, JavaScript, Ruby, Transact-SQL
  • Learning:C, Java, C++, C#, PHP, JavaScript, Ruby, Transact-SQL, Assembly, Scheme, Haskell, Others

Posted 03 September 2009 - 11:24 AM

SQL Decision Structures

If you have programmed before, then these will be familiar. The two control structures available are: CASE and IF.

If... Else

These functions return a value depending on whether a condition is true or not.

Think of it like this:

if ((x == x) == true) {
	return "X"
} else {
	// false
	return "Y";
}

The generic syntax for this function is:

IF (expr,val1,val2)

The expression that we are testing is the first parameter. If expr is true, then the return value is val, otherwise the return value is val2.

Example:

SELECT IF(5>2,'a',4)

If 5 is greater than 2, the return value is 'a'. If 5 is less than 2 the return value is 4. Since 5 is greater than 2, the return value is 'a'.

Another example:

SELECT IF(3>4,'greater','less')

Result:

less


You can nest if structures by including an if structure as one of the return values. If you have used Excel before then this notation will be familar to you. You can make more complicated statements using AND and or operators.

This might be useful when you want to return data from one table, if a certain condition is true or from a different table if the condition is false.

Switch Case

The switch case structure is a concise method of testing one variable against multiple values.

The generic syntax is:

SELECT CASE value WHEN compareValue THEN result WHEN compare2 THEN result ELSE result END;

Example:

SELECT CASE 1 WHEN 5 THEN '5' WHEN 4 THEN '4' WHEN 3 THEN '3' ELSE 1 END;

Output:

1


This is a useless example but you might want to test a value that the user has entered and return a certain value depending on what the value is, or insert it into a different table depending on the value.
  • 2

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 03 September 2009 - 11:49 AM

CASE can be really handy with metadata with certain types of databases. Some of them represent things like VARCHAR as odd numeric codes internally. +rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 05 September 2009 - 06:01 AM

I've rarely used if/else and I don't think I even knew case structures existed in SQL! Nice one, +rep.
  • 0