There are many functions built in to SQL 2000.  I’m not going to cover them all, since many of them you won’t have a need for until you really dig in deep with SQL programming.  But there are seven you should really be familiar with.

CASE

You have no idea how much you’ll use the case expression until you’ve been writing queries and reports for almost three years.  Any time you want to show something based on a decision tree, you’re going to break out a CASE statement.  The easiest way to understand the CASE statement is think of it as an IF .. THEN statement for your SELECT, WHERE, or GROUP BY clauses.

Try putting IF .. THEN into one of those, and you’d get an error.  There are two ways to construct a CASE statement, let’s start with the simple CASE.

CASE column|@variable 
 WHEN value THEN 
   <do something>
 ELSE 
   <do something else>
 END

In this case you would CASE on a column or @variable.  For each case you were testing for a certain value you would list a WHEN value THEN, and list the sql you wanted to do.  If you wanted an ELSE case (a catch all statement), you can add it, and the code for what you would like to do.  I’m sure this all sounds very abstract, so let’s do a simple example.

DECLARE @input INT
SET @input = 1

SELECT
  CASE @input
    WHEN 1 THEN
      'one'
    WHEN 2 THEN
      'two'
    ELSE
      'some other number'
  END as output

output
------
one

You can get very complex with a case statement, and that’s why there is another way to construct CASE statements.  The following method allows you to have multiple comparisons going on.  When making complex case statements, I find working from the least restrictive choice to the most restrictive choice. This makes it easier for me to track logic errors.

Searched CASE function:

CASE  
 WHEN boolean test THEN 
   <do something>
 ELSE 
   <do something else>
 END

Again, this seems very abstract so let me explain.  This second method allows you to indicate a different boolean test each time.  The first method only allowed you to test against one variable or column at a time.  With this method you could compare multiple columns at different times, or all at once.  Let’s go back to the previous example, and make it a bit more complex.
DECLARE @input INT
DECLARE @input2 CHAR
SET @input = 1
SET input2 = 'a'

SELECT
  CASE WHEN @input = 1 THEN 'one' ELSE
     CASE WHEN @input = 2 AND @input2 = 'a' THEN 'two - a' ELSE
        'some other number'
     END
  END as output

output
------
one

The most important thing to take away from this is you can use CASE to you make choices in what is output.  A very useful feat!
CAST and CONVERT
When you develop tables in a database, you generally try to find the best-fit datatype for a column.  If you’re storing age, you’d pick a SMALLINT, if you were storing a birthdate, you’d choose a DATETIME.  But when you are asked to pull that data out of the database, you never know how they will need that data.  That’s why you have the CAST and CONVERT functions.  They both let you go from one datatype to another.
There are some notable conversions that are not allowed.  Those are marked with the white circles in the graphic below.

Using CAST and convert is easy!
CAST(expression AS datatype)
CONVERT(datatype, expression)
Expression could be a variable, a literal, or a column.  A data type is any valid data type SQL knows.  See the chart above for valid data types.   In future posts, I’ll introduce you to more complex CAST and CONVERT functions.  Wait until you see what all you can do with a DATETIME!
ISNULL and NULLIF
Dealing with NULLs can be as easy or difficult as you make it.  When you do joins, and you join to a table without matching records, you have to deal with those NULLs.  Sometimes you can’t just swap your JOIN from a LEFT JOIN to an INNER JOIN.  In those cases, it’s nice to be able to use the ISNULL function.  It let’s you substitute a value where one is not found.
ISNULL( check_expression, replacement_value)
The check expression can be any variable, column, or literal.  The replacement value is what will be substituted if the check_expression is NULL.  This can become very useful when using SUM, AVERAGE, or almost any other aggregate function.  The reason is NULL * anything is NULL, NULL + anything is null… it’s like a blackhole that way.
Now, there are times when you want to consider a case to be a NULL case.  For example, you’re testing this one column, and you have blank values and NULL values, but you want to treat the blanks as NULLs.  In that case, you can use NULLIF.
NULLIF(expression, expression)
Basically you pass it any two variables, columns, or literals, and if they are equal, this function will return NULL.  This may not make complete sense the first time you read it, but the first time you use it, it’ll stick with you forever!
@@IDENTITY and @@ROWCOUNT
@@IDENTITY and @@ROWCOUNT are very special functions.  If you run the following code, right after inserting a row to a table with an IDENTITY column, you’ll see the IDENTITY that was just inserted.
SELECT @@IDENTITY
If you run the following code after running an INSERT on any table, you’ll see how many rows were inserted.
SELECT @@ROWCOUNT
These will really become helpful when you start writing STORED PROCEDUREs and need to take different steps based on INSERT statements.  For now, I just want to introduce them.  I’ll show you how to master them in later posts.
Conclusion
This will be the next to the last of the functions I teach you at the 100 level.  There are many more functions to learn, but you need a little more training before you’re ready to move on to those.  If you have any questions about these functions, send them in!  I’m here to help you learn as much about SQL as you wish!


Previous: Date Manipulation Functions
Next: Summarizing Data