Q&A

Q Does SQL have a GOTO statement?

A No. Well, OK, it does. I’ve never been a big fan of GOTO. Much later in the book, there will be an example that uses GOTO to save some time and solve a problem with a little more clarity. Generally speaking, however, GOTO creates more trouble than it’s worth. You can write miles of great SQL without ever coming near GOTO. Concentrate your efforts on mastering the other flow control statements, and you won’t be sorry.

Q Can I create local arrays of variables?

A No.

Q Just WHILE and IF? Aren’t there CASE statements, DO UNTIL, FOR NEXT, any of those commands?

A No. You would be surprised how much work you can do with just IF and WHILE. There is a CASE expression, but this is not used for flow control. The CASE expression is most often used for conditional execution inside a SELECT or UPDATE statement.

Q Is there a limit to the number of nested IF statements I can write?

A As of MS SQL Server v6.5, there is a limit of 294 nested IF ’s. Practically speaking, if you hit this limit in the normal course of SQL programming, something has gone horribly wrong. There is no predefined limit to nesting levels of the IF statement, so this limit may vary between platforms and versions of the SQL Server software.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  Name the three flow control statements in SQL.

2.  What does @@version return?

Exercises

1.  Change to the tempdb database. This database allows everyone to create tables and will destroy the table when the server shuts down. Create a table with one non-nullable integer column.

2.  Write a SQL program that randomly inserts a thousand integers between 0 and 20 to your table. (Hint: You will need to use the RAND() function inside a WHILE loop to calculate this.)

3.  Review: Write a query against this table that shows the integer and the number of times it appears in your table.

4.  Extra credit: How did the random generator perform? Did you get a pretty evenly distributed range of numbers? Try increasing the number of inserts to 10,000, 100,000, or 1,000,000. As the number of inserts increases, the distribution should become more uniform.