Home » SQL For Programmers » SQL IF/WHILE Blocks – SQL For Programmers

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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL For Programmers – Stored Procedure BasicsSQL For Programmers – Stored Procedure Basics Now that we've established a reason for using them, let's continue with the basic syntax of the stored procedure. I think you'll find that it is not that dissimilar to writing a procedure […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]
  • SQL For Programmers – Finding a StringSQL For Programmers – Finding a String Many times in our queries, we aren't looking for an exact match.  We are looking for one string that exists in another.  There are a couple statements available to us that will allow us […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

One Pingback/Trackback