Week 2

Day 12
Batch Programming Structures

Today you will learn about the different programming structures SQL provides to handle simple tasks such as conditional execution and looping. If you are familiar with structured programming or have a passing knowledge of C, Visual Basic, Delphi, or even Visual Basic for Applications, today will go quickly for you. If you have no programming background, be sure to spend some time on the exercises at the end.

In the first part of today’s lesson, I discuss these topics concerning variables:

•  Local and global variables

•  Uses for local variables

•  Using the PRINT statement

In the second part of today’s lesson, I discuss topics involving flow control statements, such as

•  IF... ELSE

•  BEGIN... END

•  WHILE

•  RETURN

•  The RAND() function: using it to generate random data

Local Variables

Local variables can be used to temporarily hold values inside a batch, stored procedure, or trigger. They are often used as loop counters in WHILE loops. You can create a local variable with the DECLARE statement:

declare @ctr int

The DECLARE statement is followed by the name for the variable, which is composed of an @ sign and the variable name. This is followed by the data type for the variable, in this case, an integer.

---

Tip:  If you plan to declare more than one local variable, do all declarations in the same DECLARE. Separate each variable with a comma:

declare @ctr int, @lname varchar(40), @price money

This kind of code enhancement doesn’t cost you anything, and it saves a little time whenever the code is run on the server.

---

You may declare variables of any server data type. You may not declare variables based on user-defined data types. After declaration, the variable has a value of null.

Assigning Values to a Variable

The most common way for a variable to be assigned a value is with the SELECT statement.

/* Declare the counter variable */
declare @ctr int

/* Assign it a value */
select @ctr = 1

/* Retrieve the value of @ctr */
select @ctr

(1 row(s) affected)
----------
1

(1 row(s) affected)

The DECLARE statement creates a local variable. The first SELECT assigns a value to the variable. The second SELECT displays the value of @ctr. Each time SELECT runs, it generates a row count message. The row count messages are misleading here. No rows are being changed, because no table is involved in the statements. You will receive these messages each time you use SELECT. If you want to turn them off, issue the SET NOCOUNT ON command.

---

Note:  When variables are created, they have a value of null. All local variables allow null values. If you do not initialize the variable, you may be in for trouble. Suppose you declare a counter variable and add one to it:

select @ctr = @ctr + 1

The value of null + 1 is still null. If you test the value of @ctr, it will always fail the test, but will not return an error message. See the section “Flow Control” later today for proper loop control protocol.

---

The second SELECT statement retrieves the value of the @ctr variable and returns it to the client.

What you see in this last example is two flavors of the SELECT statement. If you are assigning values to variables in a SELECT, you may not also select data for return to the client. In more technical terms, a result set is not generated when assigning values to variables. Consider this (incorrect) example:

/*
      BAD SYNTAX! Professional programmer on a closed server.
      Don’t try this at home.
*/
select @ctr = 1, au_lname
from   authors

Msg 141, Level 15, State 1
A SELECT statement that assigns a value to a variable
      must not be combined with data-retrieval operations.

This error message is slightly misleading, because you can assign table values to variables. What the message really means is: “A SELECT statement that assigns a value to a variable can’t also return data to the client.”

Assigning Variables a Value from a Table

You may assign a variable a value that is selected from a table. This example retrieves the price for a specific book:

declare @price money

select @price = price
from   titles
where  title_id = ‘BU1111’

(1 row(s) affected)

Because this SELECT statement is assigning a value to @price, it does not return data. Only one row matches the WHERE clause, so its price is assigned to the @price variable.

If the query returns more than one row, the value for the last row in the result set is placed in @price. If the query returns no rows, the variable’s value is unchanged.

What Are Local Variables Good For?

Local variables are very useful as counters. A counter will let you perform a task a given number of times; for example, insert to the testdata table 1000 times. Local variables are used to hold the values of global variables, explained in the section titled “Global Variables,” before the server changes the global value.

They are also useful for holding column values from tables for testing. In the last example, the price was selected from the titles table. Based on that value, you could take different actions. If the price is under ten dollars, execute a procedure; if it is over ten dollars, print an error message.

Some of the more common uses for local variables:

•  Counters in WHILE loops.

•  Hold the return values of stored procedures. A stored procedure may return an integer value to indicate whether the procedure succeeded or failed, and why it might have failed. Local variables must be used to hold and test these values.

•  Hold a value from a table for use later in the batch or procedure, without having to select the data from the table again.

•  Pass parameters in and out of stored procedures.

Local variables are necessary to do any serious work with SQL stored procedures and triggers. As the SQL you write becomes more complex, variables give you a way to keep specific values around for a while.