What you need: SQL Server 7.0

In this article, I'll discuss some Transact-SQL (T-SQL) programming constructs you can use to decide where and when to implement the conditional logic for your applications. Specifically, I'll introduce a sample stored procedure that illustrates many T-SQL concepts and programming tools, including variable types, select statements, and built-in functions.

First, let's look at variables. Variables exist in all programming languages. All user-defined variables in T-SQL are local variables because they maintain their value only for the duration of the batch or stored procedure they are declared in. You must declare variables using the Declare keyword before you can reference them. You designate variables with a single @ sign followed by the name of the variable. Once you declare a variable, you can assign a value to it using either the Select or the Set keywords:

DECLARE @AuthorName varchar(50)
Set @AuthorName = 'Marjorie Green'

or:

DECLARE @AuthorName varchar(50)
SELECT @AuthorName = au_lname from 
	authors where au_id = '213-46-8915'

The Select statement can retrieve only one field when you use it to assign a value to a variable. This code results in an error because it returns more than one field:

SELECT @AuthorName = au_fname, au_lname 
FROM authors WHERE au_id =
	'213-46-8915'

However, you can use the '+' operator to concatenate the two fields into one field in this Select statement:

SELECT @AuthorName = au_fname + ' ' + 
	au_lname FROM authors WHERE au_id =
	'213-46-8915'

	Figure 1 Debug Stored Procedures. Click here.

You will not receive an error message if you try to assign a value to a variable using a Select statement that returns more than one row. If your select statement returns more than one row, the value assigned to the variable will be the value of the last row returned.

Harness T-SQL's Power
Controlling the flow of logic is crucial to any programming language. You can control the logic flow in Transact-SQL primarily with the If…Else clause, the Case statement, and the While loop. In the sample stored procedure, an If…Else statement determines whether a record with the given au_id field already exists in the table (see Listing 1). If the record is not in the table, the stored procedure executes an Insert SQL statement. Otherwise, the stored procedure executes an UPDATE SQL statement to update the existing record in the table with the given key value.

The While loop is the only loop available in T-SQL and is similar to the VB While loop. The Break keyword lets you exit out of a While loop, and the Continue keyword lets you restart a While loop. The Case statement enables you to return different expressions based on the value of a given condition. Suppose you want to examine the value stored in the Contract field of the Authors table in the Pubs database. Because this field stores a bit value, the data in the field is either 0 or 1. The Case statement evaluates the value in the field and returns a string that provides a more user-friendly explanation of the value stored in the field:

SELECT 
CASE contract
	WHEN 0 Then 'No Contract on File'
	WHEN 1 Then 'Contract on File'
END
FROM authors

T-SQL includes several built-in functions to manipulate dates and strings and perform mathematical functions (see Table 1). The GetDate() function, one of the most common date functions, returns the current system date and time. The DateDiff() function determines how much time exists between two date values. (Refer to the SQL Server Books Online, which comes with SQL Server, for a complete list of the time increments you can use with the DATEDIFF() function.) Use these two date functions to select only the sales information for titles in the Pubs database that have sold within the past month. This code specifies the "month" keyword, so you can see the difference between the two dates expressed in terms of months:

SELECT *
FROM sales
WHERE 
DATEDIFF(month, ord_date, GETDATE())< 1

Many built-in functions for manipulating T-SQL strings are similar to functions in other programming languages such as Visual Basic. Like Visual Basic, T-SQL has a Len() function that calculates the length of a given string, and Left() and Right() functions that extract a given number of characters from the left or right end of a string. In addition to functions common to other languages, T-SQL has some unique built-in string functions as well. For example, the Difference() function returns the difference between the Soundex values for two strings. By comparing the Soundex values, you can determine how similar two strings sound when spoken. This function helps when you have business applications that allow users to search for particular values. For example, if your app lets users search for authors in the Pubs database with last names similar to the name specified, you could use the Like keyword to perform the search, but this functionality is limited. If the user enters "Carson" as the search string, this SQL, which uses the LIKE keyword, returns only one name, "Cheryl Carson":

SELECT au_fname, au_lname
FROM authors
WHERE au_lname LIKE 'Carson%'

However, this code uses the Difference() function and returns two records, "Cheryl Carson" and "Livia Karsen":

SELECT au_fname, au_lname
FROM authors
WHERE 
DIFFERENCE(au_lname, 'Carson') >= 3

Once you start implementing T-SQL programming tools, your stored procedures and triggers become more complex. When this happens, the concept of error handling becomes more important. @@Error is a built-in function that returns the error number for the most recently executed SQL statement. You can determine whether the statement executed successfully by checking to see if the value of @@Error is zero after you execute a T-SQL statement. You can look up @@Error numbers and error messages in the sysmessages system table. If @@Error does not return zero, you can handle the error by navigating to an error-handling routine (see Listing 1), by using the Return statement to terminate the query and pass a specific return code back to the calling application, or by evoking the RaiseError command. You can use RaiseError to generate specific, user-defined errors. RaiseError lets you specify error numbers, error messages, and severity levels. In addition, you can control whether information about the error is sent to the SQL Server error log or the Windows NT error log. You can find more information about the RaiseError command in the Books Online.

As your T-SQL queries become more complex, debugging them becomes difficult. No effective tools exist for debugging complex queries within SQL Server. However, the database interfaces available in Visual Basic and Visual InterDev provide more robust debugging features for your stored procedures, including the ability to step through your procedures and monitor various parameter and variable values. You can check parameter and variable values either by creating a database project in Visual InterDev or by using the data view window in Visual Basic (see Figure 1). Once you're familiar with all the T-SQL programming options, you can decide where to apply your business logic and restructure your T-SQL to limit the amount of data you send back to your calling machine. You can also minimize the number of calls you need to make from the calling application to the database.


Mary V. Hooke is the president of Distinctive Development, a software consulting firm located in the New York City area. Reach Mary by e-mail at mhooke@DistinctiveDevelopment.com.