.NET Answers

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.

Other Related Items:

Inside Microsoft SQL Server 2005 - T-SQL Querying - Reference Book (34125F)Item #: 34125F. Take a detailed look at the internal architecture of T-SQL - and unveil the power of set-based querying - with comprehensive reference... Read More >

MS-SQL Server 7.0: Dominando Seus Dados

Programming Visual Basic .NET and ADO.NET with SQLLearn how to program Visual Basic .NET and ADO.NET for Microsoft Access and SQL Server databases as you watch DVDs on your TV or laptop DVD player. Th... Read More >

Related Post

• SQL SELECT CASE Instead of IIF
• SQL – Filtering WHERE condition on two rows
• Random in SQL – SQL For Programmers
• SQL CURSOR Performance – SQL For Programmers
• SQL CURSOR – SQL For Programmers

Bookmark to:

Hide Sites

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