SQL WHILE – SQL For Programmers

The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.

Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no such thing as a FOR loop or a DO WHILE loop.  So, you have to force WHILE to do those for you.

The basic syntax of WHILE looks like this:

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

So if you want a FOR/NEXT loop, you’ll need to write:

DECLARE @someInt as int
SET @someInt = 0
WHILE @someInt < 20
  BEGIN
   /* useful code here */
   SET @someInt = @someInt + 1
  END

and a DO WHILE loop would be something like:

DECLARE @someInt as int
SET @someInt = 0
WHILE @someInt = 0
  BEGIN
   /* useful code here */
   IF *some exit condition */
     SET @someInt = 1
  END

Once you learn to substitute those constructs for your normal FOR/NET or DO WHILE code, it becomes rather easy to deal with.

Now if they’d just replace BEGIN with { and END with } I think I could live with this.

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 in any o...
  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed. There are actually two parts to this qu...
  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database. Naturally, as a programmer, I started looking for the SQL random f...
  • 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 don't really ...
  • SQL – Filtering WHERE condition on two rowsSQL – Filtering WHERE condition on two rows I received the following question a couple of days ago from a programmer using the "Ask  a Question" form which you can access from the menu of this blog: Given the following table: Name...