Control the Flow

Using control-of-flow T-SQL statements in Access projects

Some Access developers moving to SQL Server will find T-SQL and stored procedures wonderful but mysterious. Access developer experience with Jet SQL typically focuses on preparing select, update, add, and delete statements and parameter queries. SQL Server offers richer functionality through T-SQL in its stored procedures and in its batches that run in Query Analyzer. Access projects allow stored procedures but don't support Query Analyzer. T-SQL control-of-flow statements, such as IF...ELSE and BEGIN...END, let you conditionally execute blocks of T-SQL statements. Other control-of-flow statements facilitate looping through a block of code and pausing for an interval before code execution. SQL Server Books Online (BOL) documents the full list of control-of-flow statements. By using these control-of-flow statements with other T-SQL statements, ADO procedures, and Access forms, developers can incorporate useful capabilities in their custom solutions.

Control-of-Flow Keywords

The IF keyword lets you conditionally execute one other T-SQL statement. An IF statement starts with the IF keyword, followed by a Boolean expression that evaluates to true or false. If the condition is true, the statement transfers control to the next line. Otherwise, the IF statement transfers control to the line after the next one. Unlike similar statements in Visual Basic (VB), no Then keyword comes at the end of a line that starts with IF in T-SQL.

If you need to require the conditional execution of two or more lines, you create a BEGIN...END block of statements after an IF statement. The BEGIN keyword most often appears alone on a line immediately after the IF statement. On lines following BEGIN, you can enter other T-SQL statements that perform some operation. After the last operational statement in a block, insert the END keyword alone on a line. When the condition for the IF statement evaluates to true, all the statements between BEGIN and END execute as a group. A false condition causes the bypass of the whole block of statements.

You can also use the IF keyword with the ELSE keyword in an IF...ELSE statement to impose a condition. The IF clause, namely the keyword and its condition, of the IF...ELSE statement has the same syntax as the IF statement alone. (An IF statement contains an IF clause and optionally an ELSE clause.) The ELSE keyword appears after the T-SQL line or after the BEGIN...END block following the IF clause. The ELSE keyword appears on its own line without any arguments. However, this keyword must have at least one T-SQL statement after it. You can use a BEGIN...END block instead of a T-SQL line. In an IF...ELSE statement, the IF clause behaves differently from the IF statement by itself. The IF clause in an IF...ELSE statement transfers control to the single line or BEGIN...END block following the clause when its condition is true. After those lines finish executing, control passes to the statement following the line or block after the ELSE keyword. Your stored procedures can have nested IF tests within either part of an IF...ELSE statement. T-SQL doesn't limit the number of nested levels.

RETURN, a control-of-flow keyword you're likely to use with IF...ELSE statements, immediately terminates a stored procedure. Optionally, RETURN can return an integer value to a calling procedure. Your code can set the return value with either a constant or an expression with a single value. When you place two separate RETURN statements with different integer values into the blocks following the IF and ELSE keywords, a calling procedure can discover which route a stored procedure took in an IF...ELSE statement.

IF Examples

Here are some examples of different styles for writing IF and IF...ELSE statements in T-SQL. Screen 1, page 22, shows the Design view of a stored procedure in an Access project that is linked to the Pubs database. The Design view window appears within the Access project's Database window. This example shows the IF statement's basic syntax. The condition after the IF keyword consists of a SELECT statement with a COUNT function that is compared to zero. If the count of authors from California is greater than zero, a subsequent SELECT statement lists their first and last names. The stored procedure invokes the second SELECT statement only if at least one author is from California.

Screen 2 shows an example IF...ELSE statement in a stored procedure within an Access project. This example conditionally executes a SELECT statement or presents a message box. The display in Screen 2 shows the message box because no authors in the Pubs database have a Montana mailing address. You don't need a BEGIN...END block after IF or ELSE because a single logical line follows each. Three physical lines trail the IF clause, but they all belong to one SELECT statement.

The example in Screen 2 uses the RAISERROR statement to present a message box. Many code segments in BOL and other SQL Server support resources use PRINT instead of RAISERROR in situations like this. However, although PRINT works in the SQL Server Query Analyzer window, it doesn't work when you invoke a stored procedure from an Access project's Database window. RAISERROR works in both contexts. The syntax for RAISERROR is more complicated than the syntax for PRINT, and a detailed discussion of RAISERROR syntax is outside the scope of this article. But to present a message box, follow the RAISERROR keyword with an error number of at least 50000 and the message in single quotes.

Listing 1 shows a stored procedure that demonstrates how to use a BEGIN...END block with an IF...ELSE statement in an Access project. This example also shows how to use the RETURN keyword to set values that a stored procedure returns. The stored procedure accepts one input parameter, @mystate. The IF condition checks for any records with state field values matching @mystate. If such fields exist, the T-SQL code executes a SELECT query to list the first name, last name, and state for all matching records. The second statement in the BEGIN...END block assigns a value of 1 to RETURN. If it finds no matches, the procedure assigns a value of 0 to RETURN.

Running the stored procedure directly from an Access project's Database window displays either the return set of authors from a certain state or a message box saying that the stored procedure returned no records. You don't need to program the message box; it appears automatically. The RETURN value isn't immediately available, but you can retrieve it by using another stored procedure to execute the stored procedure that returns a value. Or you can use ADO parameters in a Visual Basic for Applications (VBA) procedure to collect the RETURN value from a stored procedure, as the next section illustrates.

Displaying a Return Set in an Access Form

Instead of manually invoking a stored procedure from the Database window, you can programmatically launch the stored procedure from a VBA procedure. This approach has at least three advantages. First, it lets you use VBA statements to control the stored procedure, which benefits developers who are more familiar with VB and VBA than with T-SQL. For example, you can use VBA instead of T-SQL to assign a default value to a parameter in the stored procedure. Second, your VBA procedure can read the RETURN value from a stored procedure. Third, you can readily assign a stored procedure's return set as the record source for an Access form, thus extending the usefulness of SQL Server stored procedures.

Listing 2, shows a VBA procedure that demonstrates all three benefits. This VBA procedure, which runs the stored procedure from Listing 1, uses a simple InputBox function to gather input about which state to search for authors in. The input box has a default value, so if users don't enter a state abbreviation, the box returns the default setting. If the stored procedure has a return set, the VBA procedure assigns those records as the source for an Access form.

Listing 2 starts with a series of declarations to reserve memory for various object instances and variables the procedure uses. Next, the procedure instantiates an ADO Command object and sets its CommandText property to the name of the stored procedure from Listing 1. The procedure goes on to instantiate two parameters for the command. Instantiating a parameter for a command is a two-step process. First, the procedure invokes the CreateParameter method to generate the parameter instance. Second, it appends the instance to the Parameters collection for the command. When working with a RETURN value from a stored procedure, you must always specify the RETURN value as the first parameter in the Parameters collection.

Before executing the command that references the stored procedure, the VBA procedure prompts the user for a state to search for authors in. The InputBox function statement demonstrates how to set a default value of CA to return to the input box. The VBA procedure assigns the value from this box to the second parameter.

After executing the Command object, the VBA procedure instantiates an ADO Recordset object. The next statement checks whether the stored procedure's RETURN value is 1, meaning that the stored procedure has a return set. When RETURN is 1, the VBA procedure opens a record set based on the stored procedure's return set, opens a form named frmFromSQLServer, and sets the form's Recordset property to that record set. After assigning the stored procedure's record set to the form, the procedure closes the record set. If the RETURN value isn't 1, the VBA procedure presents a message box informing the user that the stored procedure returned no records. The VBA procedure cleans up by setting object instances to Nothing. ("The Power of Two: Office 2000 and SQL Server 7.0," August 1999, includes detailed instructions on using ADO record sets with Access forms.)

At the end of the VBA procedure, the frmFromSQLServer form is open and has a record source that the stored procedure in Listing 1 specified. When the user closes the form, the assignment of the record set to the form's record source doesn't persist. The next time the form opens, the application will have to set its record source again.

Knowing how to use T-SQL's control-of-flow statements empowers Access developers to write code that operates conditionally depending on user input. And populating Access forms with return sets from stored procedures makes the results of SQL Server databases available to millions of Access users.