SQL IF/WHILE Blocks – SQL For Programmers

other-012 OK, all you programmer types.  Here’s where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, update or delete statement.  And if we stopped here, you’d probably be able to do 80% of the work you need to do.

You’d also start to wonder why you should even bother.

So now we tackle the basics of conditional programming in SQL.

The good news is that the two commands you are going to use for conditional programming are pretty simple.  The bad news is, all the features and functionality you are used to having in just about any other programming language are gone.

So let’s take a look at your basic IF statement, which is simple in any language:

DECLARE @someString as VARCHAR
IF @someString='ABC'
   SELECT * FROM someTABLE

Obviously, I’m skipping some steps.  But let’s assume that @someString was actually passed in.  I just declared it so you could see that it is a VARCHAR.

This is about as simple as it gets.  If @someString equals “ABC” then go ahead and get the data.

The trick is, what if you want to do multiple lines?

In that case, you’ll need a BEGIN and END statement:

DECLARE @someString as VARCHAR
IF @someString='ABC'
BEGIN
   SELECT * FROM someTABLE
   SELECT * FROM someOtherTABLE
END

BEGIN and END are the opening curly brace and closing curly brace of the SQL programming world.

In the real world, we’d be storing data into variables.  But, I think we’re all programmers enough to be able to look at the general idea.

Where the syntax starts to start looking a bit messy is when you use ELSE:

DECLARE @someString as VARCHAR
IF @someString='ABC'
BEGIN
   SELECT * FROM someTABLE
   SELECT * FROM someOtherTABLE
END
ELSE
BEGIN
   SELECT * FROM elseTABLE
END

Even with a bit of indenting, it doesn’t clean it up much:

DECLARE @someString as VARCHAR
IF @someString='ABC'
  BEGIN
   SELECT * FROM someTABLE
   SELECT * FROM someOtherTABLE
  END
ELSE
  BEGIN
   SELECT * FROM elseTABLE
  END

But this is what we have to live with if we want to program in SQL.

Next time we look at SQL, we’ll take a look at WHILE loops.

Related Post

3 Responses to “SQL IF/WHILE Blocks – SQL For Programmers”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear