Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 14 -
Managing Stored Procedures and Using Flow-Control Statements

Programming languages were built around flow control, but databases were built around data. Flow-control statements were added to databases to facilitate the writing of stored procedures.
Return codes or visual output to the user can increase the effectiveness of a stored procedure.
Variables must be assigned a data type and a scope. Find out how you declare your variables and control how they are used within your stored procedures.

As your systems become more complex, you'll need to spend more time carefully integrating SQL code with your host application code. In this chapter, you review the logic and flow control statements that you have available to you in your SQL code.

At a high-level, Stored Procedures are a way that you can create routines and procedures that are run on the server, by server processes. These routines can be started by an application calling them, or called by data integrity rules or triggers.

The benefit of Stored Procedures comes from the fact that they run within the SQL Server environment on the server. Although at first this might not seem to be any obvious advantage, it goes to the heart of the client-server model. Remember the rule of thumb that the system doing the work to satisfy a given situation should be the system most suited for that work? Since SQL Server manages the databases in your system, it makes sense that it would be the best place to run the stored procedures against that data. Stored procedures can return values, modify values, and can be used to compare a user- supplied value against the pre-requisites for information in the system. They run quickly, with the added horsepower of the average SQL Server hardware, and they are database-aware and able to take advantage of SQL Server's optimizer for best performance at runtime.

You can also pass values to a stored procedure, and it can return values that are not necessarily part of an underlying table, but are, instead, calculated during the running of the stored procedure.

The benefits of stored procedures on a grand scale include:


NOTE: It's important to keep the client/server model in mind when you're building your systems. Remember, data management belongs on the server, and data presentation and display manipulation for reports and inquiries should reside on the client in the ideal model. As you build systems, be on the lookout for those items that can be moved to the different ends of the model to optimize the user's experience with your application.

Although SQL is defined as a non-procedural language, SQL Server permits the use of flow-control keywords. You use the flow-control keywords to create a procedure that you can store for subsequent execution. You can use these stored procedures to perform operations with an SQL Server database and its tables instead of writing programs using conventional programming language, such as C or Visual Basic.

Some of the advantages that Stored Procedures offer over dynamic SQL Statements are:

Defining Stored Procedures

You use the CREATE PROC[EDURE] statement to create a stored procedure. Permission to execute the procedure that you create is set by default to the owner of the database. An owner of the database can change the permissions to allow other users to execute the procedure. The maximum stored procedure name length is 30 characters. The syntax that you use to define a new procedure is as follows:

CREATE PROCEDURE [owner,] procedure_name [;number]
[@parameter_name datatype [=default] [OUTput]
...
[@parameter_name datatype [=default] [OUTput]
[FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION
AS sql_statements


CAUTION: Be sure you reload your stored procedures again after information has been saved in the database tables that represents, both in volume and content, the information that your application can expect to see. Because stored procedures are compiled and optimized based on the tables, indexes, and data loading, your query can show significant improvement just by reloading it after "real" information has been placed in the system.

In Listing 14.1, a simple procedure is created that contains a SELECT statement to display all rows of a table. After the procedure is created, its name is entered on a line to execute the procedure. If you precede the name of a stored procedure with other statements, you use the EXEC[UTE] procedure name statement to execute the procedure.

Listing 14.1 Creating and Running a Stored Procedure

create procedure all_employees
as select * from employees

exec all_employees

name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514
( 2 row(s) affected)


TIP: If your call to the stored procedure is the first in your batch of commands, you don't have to specify the EXEC[UTE] portion of the statement. You can simply call the procedure by name, and it will be executed automatically.


NOTE: As mentioned earlier, naming conventions for SQL objects are an important part of your implementation plan. In a production system, you will often have hundreds of stored procedures, many tables, and many more supporting objects. You should consider coming up with a naming convention for your stored procedures that will make it easy to identify them as procedures and will make it easier to document them. In many installations, a common prefix for the stored procedure name is sp_.

You can create a new procedure in the current database only. If you're working in ISQL or ISQL/W, you can execute the USE statement followed by the name of the database to set the current database to the database in which the procedure should be created. You can use any Transact-SQL statement in a stored procedure with the exception of CREATE statements.

When you submit a stored procedure to the system, SQL Server compiles and verifies the routines within it. If any problems are found, the procedure is rejected and you'll need to determine what the problem is prior to re-submitting the routine. If your stored procedure references another, as yet unimplemented stored procedure, you'll receive a warning message, but the routine will still be installed.

If you leave the system with the stored procedure that you previously referred to uninstalled, the user will receive an error message at runtime.


NOTE: Stored procedures are treated like all other objects in the database. They are, therefore, subject to all of the same naming conventions and other limitations. For example, the name of a stored procedure cannot contain spaces, and it can be accessed using the database <object> convention.

Using Parameters with Procedures

Stored procedures are very powerful, but to be most effective, the procedure must be somewhat dynamic, which enables you, the developer, to pass in values to be considered during the functioning of the stored procedure. Here are some general guidelines for using parameters with stored procedures:

You can use parameters to pass information into a procedure from the line that executes the parameter. You place the parameters after the name of the procedure on a command line, with commas to separate the list of parameters if there is more than one. You use system datatypes to define the type of information to be expected as a parameter.

In Listing 14.2, the procedure is defined with three input parameters. The defined input parameters appear within the procedure in the position of values in the VALUE clause of an INSERT statement. When the procedure is executed, three literal values are passed into the INSERT statement within the procedure as a parameter list. A SELECT statement is executed after the stored procedure is executed to verify that a new row was added through the procedure.


NOTE: When a procedure executes as the first statement in a series of statements, the procedure does not have to be preceded by the keyword EXECUTE. The name of the procedure to be executed is simply placed as the first keyword on the line.


TIP: Be sure to check the documentation for the host language you are using with SQL Server to determine the correct calling sequence for the host language. Actual calling syntax varies from language to language.

Listing 14.2 Creating a Stored Procedure with Input Parameters

create procedure proc4 (@p1 char(15), @p2 char(20), @p3 int) as
insert into Workers
values (@p1, @p2, @p3)

proc4 `Bob Lint',Sales,3333

select * from Workers
where Badge=3333

Name                           Department      Badge
------------------------------ --------------- -----------
Bob Lint                       Sales           3333

(1 row(s) affected)

The semicolon and integer after the name of a procedure enables you to create multiple versions of a procedure with the same name. In Listing 14.3, two procedures with the same name are created as versions one and two. When the procedure is executed, the version number can be specified to control the version of the procedure that is executed. If no version number is specified, the first version of the procedure is executed. This option is not shown in the example above, but is available if needed by your application. Both procedures use a PRINT statement to return a message that identifies the procedure version.

Listing 14.3 Using Versions of Stored Procedures

create procedure proc3;1 as
print `version 1'

create procedure proc3;2 as
print `version 2'

proc3;1

version 1

proc3;2

version 2

proc3

version 1

In the previous example, proc3 is executed without preceding it with the keyword EXECUTE because it is executed as the first statement on a line.


TIP: You can use the SET NOEXEC ON command the first time that you execute a procedure to check it for errors. This prevents you from executing it when errors may cause it to fail.

You can create a new stored procedure through the SQL Enterprise Manager, as well as in ISQL or ISQL/W. Perform the following steps to create a new stored procedure through the SQL Enterprise Manager:

1. Select Stored Procedures under the Objects of the selected database in the Server Manager window.

2. Right-click Stored Procedures and select New Stored Procedures from the menu. You can also select Stored Procedures from the Manage menu to bring up the Manage Stored Procedures dialog box. You can enter Transact-SQL statements in the dialog box. The Manage Stored Procedures dialog box is brought up with the keys that are used to define a stored procedure. Figure 14.1 shows the Manage Stored Procedures dialog box before any statements are typed into the dialog box.

FIG. 14.1
You can also edit an existing stored procedure in the Manage Stored Procedures dialog box.

3. You must overwrite <PROCEDURE NAME> in the Manage Stored Procedures dialog box with the name of your new procedure.

4. Click the Execute button to create and store your procedure. Figure 14.2 shows a simple Transact-SQL statement and a new procedure name entered in the Manage Stored Procedures dialog box.

FIG. 14.2
Click the Procedures list box to display a list of the procedures in the selected database.

Calling Stored Procedures from Your Application

When you call stored procedures from other applications environments, there are a few useful tricks that you should know about. For starters, when your stored procedures take parameters, you have a couple of different options.

First, you can always provide all parameters in the order in which they are declared. Although this is easy to develop for, consider carefully whether this makes sense in the long run. There will probably be cases where you want to make a multipurpose stored procedure that calls for more parameters than would be required, on the whole, for any given call. In those cases, you're expecting to have some parameters that are not specified in each call.

You use a test for null on a parameter to determine whether it was provided. This means you can test directly against Null, or you can use the IsNull comparison operator.


See Chapter 9, "Using Functions," in the section titled "Using ISNULL and NULLIF," for more information about ISNULL.

On the application side, it can be quite cumbersome to have to specify each value on every call to the stored procedure, even in cases where the value is NULL. In those cases, the calling application can use named arguments to pass information to SQL Server and the stored procedure. For example, if your stored procedure allows up to three different arguments, name, address, and phone, you can call the routine as follows:

Exec sp_routine @name="blah"

When you provide the name of the argument being passed, SQL Server can map it to its corresponding parameter. This is, typically, the best way to pass information to SQL Server, and it also helps make the code more readable because you can tell which parameters are being passed.

Displaying and Editing Procedures

You use the system procedure sp_helptext to list the definition of a procedure, and sp_help to display control information about a procedure. The system procedures sp_helptext and sp_help are used to list information about other database objects, such as tables, rules, and defaults, as well as stored procedures.

Procedures with the same name, regardless of version number, are displayed together and dropped together. In Listing 14.4, the definition of procedures proc3, version one and two, are both displayed when the procedure is specified with the sp_helptext system procedure.

Listing 14.4 Viewing Different Versions of Stored Procedures

sp_helptext proc3

text
---------------------------------------------
create procedure proc3;1 as
print `version 1'
create procedure proc3;2 as
print `version 2'

(1 row(s) affected)

In Listing 14.5, the system procedure sp_help is used to display information about the procedure proc3. If the version number is used with the sp_help system procedure, an error is returned.

Listing 14.5 sp_help Only Shows the Most Current Version

sp_help proc3

Name                           Owner                          Type
------------------------------ ------------------------------ ----------------
proc3                          dbo                            stored procedure
Data_located_on_segment When_created
----------------------- ---------------------------
not applicable          Dec 7 1994  1:50PM

You can use an additional system procedure just to return information about stored procedures. The system procedure sp_stored_procedures is used to list information about stored procedures. In the following example, the procedure sp_stored_procedures is used to display information about a previously stored procedure.

sp_stored_procedures procall

procedure_qualifier  procedure_owner  procedure_name   num_input_params num_output_params num_result_sets remarks ----------------------------
master  dbo  procall;1  -1  -1 -1  (null)

(1 row(s) affected)


TIP: You can use the command SET SHOWPLAN ON before you execute a procedure to see how SQL Server will perform the necessary reads and writes to the database tables when the statements in your procedure are executed. You can use this information to help determine whether additional indexes or different data layout would be beneficial to the query.

You use the SQL Enterprise Manager to list and edit existing procedures. Double-click the procedure to be edited in the list of stored procedures in the main window of the server Manager. The selected procedure is displayed and can be changed in the Manage Stored Procedures dialog box that is displayed.

You can't directly edit stored procedures. You'll notice in the Manage Stored Procedures dialog box (refer to Figures 14.1 and 14.2) an existing procedures has additional Transact-SQL statements added prior to the procedure definition. The conditional IF statement is used to check whether the procedure is already defined and delete the procedure. The old procedure definition must be removed and a new procedure, with the name you specify, is substituted for the old procedure.

Making Changes and Dropping Stored Procedures

Two closely related tasks that you'll, no doubt, have to perform are making changes to existing stored procedures and removing no longer used stored procedures. In the next two sections, you see exactly how you accomplish both of these tasks and understand why they are so closely related.

Changing an Existing Stored Procedure Stored procedures cannot be modified in place, so you're forced to first drop the procedure, then create it again. Unfortunately, there is no ALTER statement that can be used to modify the contents of an existing procedure. This stems largely from the query plan that is created and the from fact that stored procedures are compiled after they are initiated. Because the routines are compiled, and the query plan relies on the compiled information, SQL Server uses a binary version of the stored procedure when it is executed. It would be difficult or impossible to convert from the binary representation of the stored procedure back to English to allow for edits. For this reason, it's imperative that you maintain a copy of your stored procedures in a location other than SQL Server. Although SQL Server can produce the code that was used to create the stored procedure, you should always maintain a backup copy.

You can pull the text associated with a stored procedure by using the sp_helptext system stored procedure. The syntax of sp_helptext is as follows:

Sp_helptext procedure name

For example, pulling the text associated with the all_employees stored procedure results in the display shown in Figure 14.3.

FIG. 14.3
You can review the text associated with a stored procedure with the sp_helptext statement.

Alternatively, if you want to review the stored procedure in the Enterprise Manager, you can do so by selecting the Database, Objects, Stored procedures and then double-clicking the stored procedure you want to view. The result, shown in Figure 14.4, is not only a listing of the stored procedure, but also the proper statement to drop it and insert a new copy, should you so desire.

After you have the text, you can recreate the routine with the changes you need.

FIG. 14.4
The Enterprise Manager sets up the syntax to drop and then recreate the stored procedure, if needed.

Removing Existing Stored Procedures You use the DROP PROCEDURE statement to drop a stored procedure that you've created. Multiple procedures can be dropped with a single DROP PROCEDURE statement by listing multiple procedures separated by commas after the keywords DROP PROCEDURE in the syntax:

DROP PROCEDURE procedure_name_1, ...,procedure_name_n

Multiple versions of a procedure can't be selectively dropped. All versions of a procedure with the same name must be dropped at the same time by using the DROP PROCEDURE statement that specifies the procedure without a version number.

In the following example, the two versions of the procedures proc3 are dropped:

drop procedure proc3
This command did not return data, and it did not return any rows

You can also drop a selected procedure in the SQL Enterprise Manager. Right-click the selected procedure, and choose Drop from the menu that is displayed.

Understanding Procedure Resolution and Compilation

The benefit of using a stored procedure for the execution of a set of Transact-SQL statements is that it is compiled the first time that it's run. During compilation, the Transact-SQL statements in the procedure are converted from their original character representation into an executable form. During compilation, any objects that are referenced in procedures are also converted to alternate representations. For example, table names are converted to their object IDs and column names to their column IDs.

An execution plan is also created just as it would be for the execution of even a single Transact-SQL statement. The execution plan contains, for example, the indexes to be used to retrieve rows from tables that are referenced by the procedure. The execution plan is kept in a cache and is used to perform the queries of the procedure each time it's subsequently executed.


TIP: You can define the size of the procedure cache so that it is large enough to contain most or all the available procedures for execution and save the time that it would take to regenerate the execution plan for procedures.

Automatic Recompilation

Normally, the procedure's execution plan is run from the memory cache of procedures that permits it to execute rapidly. A procedure, however, is automatically recompiled under the following circumstances:


NOTE: It's often the case that SQL Server remains up and running on the server system continuously. As a database server, it must be available whenever users on client PC workstations must access the SQL Server databases. The server computer and SQL Server need never be stopped and restarted unless a major error occurs, hardware malfunctions, or an update to a new version of SQL Server or Windows NT is in process. The recompilation of stored procedures would not be done frequently on systems that run non-stop.


TIP: When SQL Server is installed, you can specify that it should automatically restart when the server system is rebooted.

Note that because SQL server attempts to optimize stored procedures by caching the most recently used routines, it is still possible that an older execution plan, one previously loaded in cache, may be used in place of the new execution plan.

To prevent this problem, you must both drop and recreate the procedure or stop and restart SQL Server to flush the procedure cache and ensure that the new procedure is the only one that will be used when the procedure is executed.

You can also create the procedure using a WITH RECOMPILE option so that the procedure is automatically recompiled each time that it's executed. You should do this if the tables accessed by the queries in a procedure are very dynamic. Tables that are very dynamic have rows added, deleted, and updated frequently, which results in frequent changes to the indexes that are defined for the tables.

In other cases, you may want to force a recompilation of a procedure when it would not be done automatically. For example, if the statistics used to determine whether an index should be used for a query are updated, or an entire index is created for a table, recompilation is not automatic. You can use the WITH RECOMPILE clause on the EXECUTE statement when you execute the procedure to do a recompilation. The syntax of the EXECUTE statement with a recompile clause is:

EXECUTE procedure_name AS
.Transact-SQL statement(s)
...
WITH RECOMPILE

If the procedure you're working with uses parameters and these parameters control the functionality of the routine, you may want to use the RECOMPILE option. This is because, within the stored procedure, if the routine's parameters may determine the best execution path, it may be beneficial to have the execution plan determined at runtime, rather than determining it once and then using this plan for all accesses to the stored procedure.


NOTE: It may be difficult to determine whether a procedure should be created with the WITH RECOMPILE option. If in doubt, you'll probably be better served by not creating the procedure with the RECOMPILE option. This is because, if you create a procedure with the RECOMPILE option, the procedure is recompiled each time the procedure is executed, and you may waste valuable CPU time to perform these compiles. You can still add the WITH RECOMPILE clause to force a recompilation when you execute the procedure.

You can't use the WITH RECOMPILE option in a CREATE PROCEDURE statement that contains the FOR REPLICATION option. You use the FOR REPLICATION option to create a procedure that is executed during replication.


See the Chapter 17 section titled "Setting Up and Managing Replication," for more information about replication.

You can add the ENCRYPTION option to a CREATE PROCEDURE statement to encrypt the defi- nition of the stored procedure that is added to the system table syscomments. You use the ENCRYPTION option to prevent other users from displaying the definition of your procedure and learning what objects it references and what Transact-SQL statements it contains.


CAUTION: Unless you absolutely must encrypt procedures for security reasons, you should leave procedures unencrypted. When you upgrade your database for a version change or to rebuild it, your procedures can only be recreated if the entries in syscomments are not encrypted.

Defining Procedure Auto Execution

You can use the system stored procedure, sp_makestartup, to define a procedure to execute automatically at the startup of SQL Server. You can mark any number of procedures to execute automatically at startup. The syntax sp_makestartup is as follows:

sp_makestartup procedure_name

The procedures that are defined to execute automatically at startup execute after the last database has been automatically started and recovered at startup of SQL Server. You can use the system procedure sp_helpstartup to list the procedures that are defined to execute at startup. You use the system procedure, sp_unmakestartup, to prevent a procedure from executing automatically.

In Listing 14.5, a new procedure is created that is marked for the automatic execution when SQL Server is started. In addition, the list startup procedures are also listed before and after the procedures are removed from automatic execution at startup.

Listing 14.5 Creating an Automatic Execute Stored Procedure

create procedure test_startup as
print `test procedure executed at startup'
go
sp_makestartup test_startup
go
Procedure has been marked as `startup'.
sp_helpstartup
go
Startup stored procedures:
------------------------------
test_startup

(1 row(s) affected)
sp_unmakestartup test_startup
go
Procedure is no longer marked as `startup'.
sp_helpstartup

Startup stored procedures:

Understanding Procedure and Batch Restrictions

Sets of Transact-SQL statements are referred to as batches and include stored procedures. The rules or syntax for the use of Transact-SQL statements in batch apply to the following list of objects:

The syntax is primarily a set of restrictions that limit the types of statements that can be used in batch. Most of the restrictions are the statements that create objects, change the database, or query environment, and don't take effect within the current batch.

For example, although rules and defaults can be defined and bound to a column or user- defined datatype within a batch, the defaults and rules are in effect until after the completion of the batch. You can't drop an object and reference or recreate it in the same batch.

Some additional Set options that are defined with a batch don't apply to queries contained in the batch. For example, the Set option SET NOCOUNT ON will affect all queries that follow it within a stored procedure, and suppress the count line for the execution of SELECT statements. The SET SHOWPLAN ON option does not affect the queries used within a stored procedure, and a query plan isn't displayed for the queries in the procedure.

Understanding the End-of-Batch Signal GO

As you've seen throughout this book, if you use the command line ISQL for the execution of a set of Transact-SQL statements, the GO command is used to specify the end of the set of statements. GO is used on a line by itself. The GO command is required if you interactively use a set of statements or read in statements from an input file to ISQL.

The GO command is not required to execute a set of Transact-SQL statements that are used in the Windows GUI application form of ISQL, ISQL/W. GO is also not required in a series of Transact-SQL statements that are executed within batch objects, such as stored procedures, rules, defaults, triggers, or views. In Listing 14.6, the GO command is used first to cause the execution of the USE command and then to signal the end of the second batch, which contains two SELECT statements.

Listing 14.6 Working with a Batch Job in ISQL

C:>isql/U sa
Password:
1>use employees
2>go
1>select * from Workers
2>select count(*) from Workers
3>go

Name                           Department      Badge
------------------------------ --------------- -----------
Bob Smith                      Sales           1234
Sue Simmons                    Sales           3241
Mary Watkins                   Field Service   6532
Linda Lovely                   Library         7888

(4 row(s) affected)
----------

----------
4

(1 row(s) affected)

In the following example, the GO command is used with the file query1.sql, which contains the following commands:

use employees
go
select * from Workers
select max(Rownum) from Rownumber
go

The Transact-SQL statements within the file are executed with the invocation of ISQL, which returns the display shown in Listing 14.7:

Listing 14.7 Results of Running the Batch in query1.sql

isql /U /i query1.sql /n /P `'
Name                                Department               Badge
----------------------------------  ------------------------- ------------
Bob Smith                           Sales                    1234
Sue Simmons                         Sales                    3241
Mary Watkins                        Field Service            6532
Linda Lovely                        Library                  7888

(4 rows affected)

-------------
         19

(1 row affected)


TIP: You can also use a /o file-spec to direct the output of the execution of ISQL to a file instead of to your monitor and to capture the output of any statements executed during the ISQL session.

Using Flow-Control Statements

Transact-SQL contains several statements that are used to change the order of execution of statements within a set of statements, such as a stored procedure. The use of such flow-control statements permits you to organize statements in stored procedures to provide the capabilities of a conventional programming language, such a C or COBOL. You may find that some of the retrieval, update, deletion, addition, and manipulation of the rows of database tables can more easily be performed through the use of flow-control statements in objects, such as stored procedures.

Using IF...ELSE

You can use the keywords IF and ELSE to control conditional execution within a batch, such as a stored procedure. The IF and ELSE keywords enable you to test a condition and execute either the statements that are part of the IF branch or the statements that are part of the ELSE branch. You define the condition for testing as an expression following the keyword IF. The syntax of an IF...ELSE statement is as follows:

IF expression
     statement
[ELSE]
     [IF expression]
     statement]


NOTE: It's impossible to show examples of the use of conditional statements that can be formed with the keywords IF and ELSE without using other keywords. The examples shown next use the keywords PRINT and EXISTS. In the subsequent examples, the keyword PRINT is used to display a string of characters.

The keyword EXISTS is usually followed by a statement within parentheses when used in an IF statement. The EXISTS statement is evaluated to either True or False, depending on whether the statement within the parentheses returns one or more rows, or no rows, respectively.

You needn't use an ELSE clause as part of an IF statement. The simplest form of an IF statement is constructed without an ELSE clause. In the following example, a PRINT statement is used to display a confirmation message that a row exists in a database table. If the row doesn't exist in the table, the message, No entry, is displayed. Unfortunately, the message is also displayed after the verification message is displayed because you're not using the ELSE option.

if exists (select * from Workers
where Badge=1234)
    print `entry available'
print `No entry'

entry available
No entry

In the following example, the row isn't found in the table so only the PRINT statement that follows the IF statement is executed:

if exists (select * from Workers
where Badge=1235)
     print `entry available'
print `No entry'

No entry

The previous two examples show the problem of using an IF statement that doesn't contain an ELSE clause. In the examples, it's impossible to prevent the message, No entry, from appearing. You add an ELSE clause to the IF statement to print the No entry message, if a row isn't found and the condition after the IF isn't True.

In the following example, our previous examples are rewritten to use IF and ELSE clauses. If a row that is tested for in the IF clause is in the table, only the message, employee present, is displayed. If the row isn't found in the table, only the message, employee not found, is displayed:

if exists (select * from employees
where name='Bob Smith')
     print `employee present'
else print `employee not found'


CAUTION: Unlike some programming languages you may have used, when used alone, the Transact-SQL IF statement can have only one statement associated with it. As a result, there is no need for a keyword, such as END-IF, to define the end of the IF statement. See Using BEGIN...END in the next section for information on grouping statements and associating them with an IF...ELSE condition.

Using BEGIN...END

You use the keywords BEGIN and END to designate a set of Transact-SQL statements to be executed as a unit. You use the keyword BEGIN to define the start of a block of Transact-SQL statements. You use the keyword END after the last Transact-SQL statement that is part of the same block of statements. BEGIN...END uses the following syntax:

BEGIN
     statements
END

You often use BEGIN and END with a conditional statement such as an IF statement. BEGIN and END are used in an IF or ELSE clause to permit multiple Transact-SQL statements to be executed if the expression following the IF or ELSE clause is True. As mentioned earlier, without a BEGIN and END block enclosing multiple statements, only a single Transact-SQL statement can be executed if the expression in the IF or ELSE clause is True.

In Listing 14.8, BEGIN and END are used with an IF statement to define the execution of multiple statements if the condition tested is True. The IF statement contains only an IF clause; no ELSE clause is part of the statement.

Listing 14.8 Controlling Flow with the IF Statement

if exists (select * from employees
where badge=1234)
     begin
           print `entry available'
           select name,department from employees
           where badge=1234
     end

entry available
name                 department
-------------------- --------------------
Bob Smith            Sales

(1 row(s) affected)

In Listing 14.9, an ELSE clause is added to the IF statement to display a message if the row isn't found:

Listing 14.9 Using ELSE with an IF Statement

if exists (select * from employees
where department='Sales')
     begin
           print `row(s) found'
           select name, department from employees
           where department='Sales'
     end
else print `No entry'

row(s) found
name                 department
-------------------- --------------------
Bob Smith            Sales
Mary Jones           Sales

(2 row(s) affected)

Listing 14.10 returns the message that follows the ELSE clause because no row is found:

Listing 14.10 Output When the ELSE Option is Executed

if exists (select * from employees
where department='Nonexistent')
     begin
           print `row(s) found'
           select name, department from employees
           where department='Nonexistent'
     end
else print `No entry'

No entry

Using WHILE

You use the keyword WHILE to define a condition that executes one or more Transact-SQL statements when the condition tested evaluates to True. The statement that follows the expression of the WHILE statement continues to execute as long as the condition tested is True. The syntax of the WHILE statement is as follows:

WHILE
    <boolean_expression>
    <sql_statement>


NOTE: As with the IF...ELSE statements, you can only execute a single SQL statement with the WHILE clause. If you need to include more than one statement in the routine, use the BEGIN...END construct as described above.

In Listing 14.11, a WHILE statement is used to execute a SELECT statement that displays a numeric value until the value reaches a limit of five. The example uses a variable that is like a parameter in that a variable is a named storage location. You define the datatype of a variable using a DECLARE statement to control the way information is represented in the variable. A variable is always referenced preceded by an at sign (@) like a parameter.

In the example, the value stored in the variable is initialized to one and subsequently incre- mented. The statements associated with the WHILE execute until the variable x reaches a value of five:

Listing 14.11 Using a Variable to Control Execution

declare @x int
select @x=1
while @x<5
begin
print `x still less than 5'
select @x=@x+1
end
go
(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)

A more meaningful example of the use of a WHILE statement can be shown after two additional Transact-SQL keywords are introduced and explained. An example using WHILE along with the keywords BREAK and CONTINUE will be shown a little later in this section.

Using BREAK

You use the keyword BREAK within a block of Transact-SQL statements that is within a conditional WHILE statement to end the execution of the statements. The execution of a BREAK results in the first statement following the end of block to begin executing. The syntax of a BREAK clause is as follows:

  WHILE
    <boolean_expression>
    <sql_statement>
  BREAK
    <sql_statement>

In Listing 14.12, the BREAK within the WHILE statement causes the statement within the WHILE to terminate. The PRINT statement executes once because the PRINT statement is located before the BREAK. After the BREAK is encountered, the statements in the WHILE clause aren't executed again.

Listing 14.12 Example of Using a Variable to Control Execution

declare @x int
select @x=1
while @x<5
begin
     print `x still less than 5'
     select @x=@x+1
     break
end

(1 row(s) affected)
x still less than 5
(1 row(s) affected)

Using CONTINUE

You use a CONTINUE keyword to form a clause within a conditional statement, such as a WHILE statement, to explicitly continue the set of statements that are contained within the conditional statement. The syntax of the CONTINUE clause is as follows:

WHILE
 <boolean_expression>
 <statement>
BREAK
 <statement>
CONTINUE

In Listing 14.13, a CONTINUE is used within a WHILE statement to explicitly define that execution of the statements within the WHILE statement should continue as long as the condition specified in the expression that follows WHILE is True. The use of CONTINUE in the following example skips the final PRINT statement:

Listing 14.13 Controlling Flow with CONTINUE

declare @x int
select @x=1
while @x<5
begin
     print `x still less than 5'
     select @x=@x+1
     continue
     print `this statement will not execute'
end

(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)
x still less than 5
(1 row(s) affected)

Examples of Using WHILE, BREAK, and CONTINUE

Although the two previous examples use BREAK and CONTINUE alone, you don't typically use either CONTINUE or BREAK within a WHILE statement alone. Both BREAK and CONTINUE are often used following an IF or ELSE that are defined within a WHILE statement, so an additional condition can be used to break out of the WHILE loop. If two or more loops are nested, BREAK exits to the next outermost loop.

In Listing 14.14, a BREAK is used with an IF statement, both of which are within a WHILE statement. The BREAK is used to terminate the statements associated with the WHILE if the condition specified by the IF statement is True. The IF condition is True if the value of the local variable, @y, is True:

Listing 14.14 Controlling Flow with CONTINUE

declare @x int
declare @y tinyint
select @x=1, @y=1
while @x<5
begin
     print `x still less than 5'
     select @x=@x+1
     select @y=@y+1
     if @y=2
     begin
           print `y is 2 so break out of loop'
           break
     end
     end
print `out of while loop'

(1 row(s) affected)
x still less than 5
(1 row(s) affected)
(1 row(s) affected)
y is 2 so break out of loop
out of while loop

In Listing 14.15, a WHILE statement is used to permit only the rows of a table that match the criteria defined within the expression of the WHILE statement to have their values changed:

Listing 14.15 Using WHILE to Control Program Flow

begin tran
while (select avg(price)from titles) < $30
begin
     select title_id, price
     from titles
     where price >$20
     update titles set price=price * 2
end

(0 row(s) affected)

title_id price
-------- --------------------------
PC1035   22.95
PS1372   21.59
TC3218   20.95

(3 row(s) affected)
(18 row(s) affected)
(0 row(s) affected)

title_id price
-------- --------------------------
BU1032   39.98
BU1111   23.90
BU7832   39.98
MC2222   39.98
PC1035   45.90
PC8888   40.00
PS1372   43.18
PS2091   21.90
PS3333   39.98
TC3218   41.90
TC4203   23.90
TC7777   29.98

(12 row(s) affected)
(18 row(s) affected)
(0 row(s) affected)

You must be careful when defining the WHILE statement and its associated statements. As shown in the following example, if the condition specified with the WHILE expression continues to be True, the WHILE loop executes indefinitely.

while exists (select hours_worked from pays)
print `hours worked is less than 55'

(0 row(s) affected)
hours worked is less than 55
(0 row(s) affected)
...

If the evaluation of the expression following the WHILE returns multiple values, you should use an EXISTS instead of any comparison operators. In the following example, the error message that is returned is descriptive of the problem:

while (select hours_worked from pays) > 55
print `hours worked is less than 55'

Msg 512, Level 16, State 1
Subquery returned more than 1 value.  This is illegal when the subquery follows  =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.

In this case, you'll want to use EXISTS to determine the comparison value.

Defining and Using Variables

You may recall that earlier in this chapter variables were described as similar to parameters in that they are named storage locations. Variables in Transact-SQL can be either local or global. You define local variables by using a DECLARE statement and assigning the variable a datatype. You assign an initial value to local variables with a SELECT statement.

You must declare, assign a value, and use a local variable within the same batch or stored procedure. The variable is only available for use within the same batch or procedure, hence the name local variable.

You can use local variables in batch or stored procedures for such things as counters and temporary holding locations for other variables. Recall that local variables are always referenced with an @ preceding their names. You can define the datatype of a local variable as a user-defined datatype, as well as a system datatype. One restriction that applies to local variables is that you can't define a local variable as a text or image datatype.

The syntax of a local variable is as follows:

DECLARE @variable_name datatype [,variable_name datatype...]

The SELECT statement is used to assign values to local variables, as shown in the following syntax:

SELECT @variable_name = expression |select statement
[,@variable_name = expression select statement]
[FROM list of tables] [WHERE expression]
[GROUP BY...]
[HAVING ...]
[ORDER BY...]

If the SELECT statement returns more than a single value, the variable is assigned to the last value returned. In Listing 14.16, two local variables are defined and used to return the number of rows in the table. The CONVERT function must be used to convert the numeric format of the number of rows to a text datatype for the PRINT statement. The message that is displayed by the PRINT statement is first built and assigned to a local variable because the concatenation can't be done within the PRINT statement:

Listing 14.16 Storing Values into Variables

declare @mynum int
select @mynum = count(*)from Workers
declare @mychar char(2)
select @mychar = convert(char(2),@mynum)
declare @mess char(40)
select @mess ='There are ` + @mychar + `rows in the table Workers'
print @mess

(1 row(s) affected)

(4 row(s) affected)

(1 row(s) affected)

There are 4 rows in the table Workers

Each SELECT statement returns a count message in the previous example. If you want the count message suppressed, you must first execute the SET NOCOUNT statement. In Listing 14.17, the same statements that were executed in the previous example are re-executed with the count turned off:

Listing 14.17 Storing Values into Variables (Example with SET NOCOUNT in Force)

declare @mynum int
select @mynum = count(*)from Workers
declare @mychar char(2)
select @mychar = convert(char(2),@mynum)
declare @mess char(40)
select @mess ='There are ` + @mychar + `rows in the table Workers'
print @mess

There are 4 rows in the table Workers

Using PRINT with Variables

You'll recall that in examples shown earlier in this chapter, PRINT was used to display a message to the assigned output device. You use the keyword PRINT to display ASCII text or variables up to 255 characters in length. You can't use PRINT to output datatypes other than CHAR, VARCHAR, or the global variable @@VERSION.

Recall that you can't concatenate string data in a PRINT statement directly. You must concatenate text or variables into a single variable and output the results with the PRINT statement. The syntax of the PRINT statement is as follows:

PRINT `text' |@local_variable | @@global_variable

Using Global Variables

Although your stored procedure parameters are limited in scope to the procedure in which they are defined, SQL Server has several intrinsic global variables. These variables, defined and maintained by the system, are available at any time within your stored procedures. Keep the following guidelines in mind when you work with global variables:

You reference a global variable to access server information or information about your operations. Table 14.1 lists the names of all Microsoft SQL Server global variables and a brief description of the information that is contained within them.

Table 14.1 Global Variables for Microsoft SQL Server

Global Variable Description
@@CONNECTIONS total logons or attempted logins
@@CPU_BUSY cumulative CPU Server time in ticks
@@DBTS value of unique timestamp for database
@@ERROR last system error number, 0 if successful
@@FETCH_STATUS status of the last FETCH statement
@@IDENTITY the last inserted identity value
@@IDLE cumulative CPU Server idle time
@@IO_BUSY cumulative Server I/O time
@@LANGID current language ID
@@LANGUAGE current language name
@@MAX_CONNECTIONS max simultaneous connections
@@MAX_PRECISION precision level for decimal and numeric datatypes
@@MICROSOFTVERSION internal version number of SQL Server
@@NESTLEVEL current nested level of calling routines from 0 to 16
@@PACK_RECEIVED number of input packets read
@@PACKET_SENT number of output packets written
@@PACKET_ERRORS number of read and write packet errors
@@PROCID current stored procedure ID
@@ROWCOUNT number of rows affected by last query
@@SERVERNAME name of local server
@@SERVICENAME name of the running service
@@SPID current process server ID
@@TEXTSIZE current of max text or image data with default of 4K
@@TIMETICKS number of microseconds per tick-machine independent. tick is 31.25 milliseconds/1/32 sec.
@@TOTAL_ERRORS number of errors during reads or writes
@@TOTAL_READ number of disk reads (not cache)
@@TOTAL_WRITE number of disk writes
@@TRANCOUNT current user total active transactions
@@VERSION date and version of SQL Server

In Listing 14.18, a global variable is used to retrieve the version of SQL Server, which is concatenated with a string literal and the contents of a second global variable:

Listing 14.18 Using Global Variables

PRINT @@VERSION
declare @mess1 char(21)
select @mess1 = `Server name is ` + @@servername
PRINT @mess1

Microsoft SQL Server  6.50 - 6.50.201 (Intel X86) 
Apr  3 1996 02:55:53 
Copyright  1988-1996 Microsoft Corporation

(1 row(s) affected)

Server name is Primary

Using Additional Procedure and Batch Keywords

Several additional keywords can be used within stored procedures or batches of Transact-SQL commands. These additional keywords don't fall into a single descriptive category of similar function. Some of these keywords are GOTO, RETURN, RAISERROR, WAITFOR, and CASE.

Using GOTO

You use a GOTO to perform a transfer from a statement to another statement that contains a user-defined label. A GOTO statement used alone is unconditional. The statement that contains the destination label name follows rules for identifiers and is followed by a colon (:).

You only use the label name without the colon on the GOTO line. The syntax of the GOTO statement is as follows:

label:

GOTO label

Listing 14.19 shows the use of the GOTO statement that transfers control to a statement that displays the word yes until the value of a variable reaches a specified value. The COUNT was turned off prior to execution of the statements in the example:

Listing 14.19 Using the GOTO Statement

declare @count smallint
select @count =1
restart:
print `yes'
select @count =@count + 1
while @count <= 4
goto restart

yes
yes
yes
yes

Using RETURN

You use the RETURN statement to formally exit from a query or procedure and optionally provide a value to the calling routine. A RETURN is often used when one procedure is executed from within another. The RETURN statement, when used alone, is unconditional, though you can use the RETURN within a conditional IF or WHILE statement. The syntax of the RETURN statement is as follows:

RETURN [integer]

You can use a RETURN statement at any point in a batch or procedure. Any statements that follow the RETURN are not executed. A RETURN is similar to a BREAK with one difference. A RETURN, unlike a BREAK, can be used to return an integer value to the procedure that invoked the procedure that contains the RETURN. Execution of statements continue at the statement following the statement that executed the procedure originally.

To understand the use of the RETURN statement, you must first understand the action performed by SQL Server when a procedure completes execution. SQL Server always makes an integer value available when a procedure ends. A value of zero indicates that the procedure executed successfully. Negative values from -1 to -99 indicate reasons for the failure of statements within the procedure. These integer values are always returned at the termination of a procedure even if a RETURN statement isn't present in a procedure.

You can optionally use an integer value that follows the RETURN statement to replace the SQL Server value with your own user-defined value. You should use non-zero integer values so that your return status values don't conflict with the SQL Server status values. If no user-defined return value is provided, the SQL Server value is used. If more than one error occurs, the status with the highest absolute value is returned. You can't return a NULL value with a RETURN statement. Table 14.2 shows several of the return status values that are reserved by SQL Server.

Table 14.2 Selected Microsoft SQL Server Status Values

Return Value Meaning
0 successful execution
-1 missing object
-2 datatype error
-3 process was chosen as a deadlock victim
-4 permission error
-5 syntax error
-6 miscellaneous user error
-7 resource error, such as out of space
-8 nonfatal internal problem
-9 system limit was reached
-10 fatal internal inconsistency
-11 fatal internal inconsistency
-12 table or index is corrupt
-13 database is corrupt
-14 hardware error

You must provide a local variable that receives the returned status in the EXECUTE statement, which invokes the procedure that returns status. The syntax to specify a local variable for the returned status value is the following:

EXEC[ute] @return_status=procedure_name

Listing 14.20 shows a return value from a called procedure that executes successfully and returns zero (0). The example shows the definition of the called procedure proc1. This stored procedure is executed from a set of Transact-SQL statements entered interactively.


NOTE: When a set of Transact-SQL statements execute together, whether the statements are part of a procedure or not, the rules for batch operations apply. This is true even if the set of statements are typed in interactively.


NOTE: A procedure that is invoked within another procedure with an EXECUTE statement is most often referred to as a called procedure. Call refers to an equivalent operation used in some programming languages. The keyword used in these languages to invoke the equivalent of a section of code from a program is CALL. This is the same as running a subroutine or function in these other languages.

Although the called procedure doesn't contain a RETURN statement, SQL Server returns an integer status value to the procedure that called proc1.

Listing 14.20 Showing the Results from a Stored Procedure Call When No Explicit Return Value is Defined

create procedure proc1 as
select * from employees

declare @status int
execute @status = proc1
select status = @status

name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514

(2 row(s) affected)

status
-----------
0

(1 row(s) affected)

In Listing 14.21, proc2 is identical to the procedure proc1 that was used in the previous example except that proc2 contains a RETURN statement with a user-defined positive integer value. A SELECT statement is to display the returned status value from proc2 to confirm that the specified value on the RETURN statement in proc2 is returned to the next statement after the statement that executed proc2.

Listing 14.21 Returning a Status from the Stored Procedure

create procedure proc2 as
select * from employees
return 5

declare @status int
execute @status = proc2
select status = @status

name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514

(1 row(s) affected)

status
----------
5

(1 row(s) affected)

In Listing 14.22, the returned value is checked as part of a conditional statement. A message is displayed if the procedure executed successfully. This third example of Transact-SQL return statements is more typical of the usage of return status in a production environment.

Listing 14.22 Linking Stored Procedures Based on Return Values

declare @status int
execute @status = proc1
if (@status = 0)
begin
     print `'
     print `proc1 executed successfully'
end

name                 department           badge
-------------------- -------------------- -----------
Bob Smith            Sales                1234
Mary Jones           Sales                5514

proc2 executed successfully


TIP: You can nest procedures within other procedures up to 16 levels in Transact-SQL.

Using RAISERROR

You use the RAISERROR statement to return a user-specified message in the same form that SQL Server returns errors. The RAISERROR also sets a system flag to record that an error has occurred. The syntax of the RAISERROR statement is as follows:

RAISERROR (<integer_expression>|<`text of message'>, [severity] [, state]
 [, argument1] [, argument2] )
[WITH LOG] 

The integer_expression is a user-specified error or message number and must be in the range 50,000 to 2,147,483,647. The integer_expression is placed in the global variable, @@ERROR, which stores the last error number returned. An error message can be specified as a string literal or through a local variable. The text of the message can be up to 255 characters and is used to specify a user-specified error message. A local variable that contains an error message can be used in place of the text of the message. RAISERROR always sets a default severity level of 16 for the returned error message.

In Listing 14.23, a local variable is defined as a character datatype that is large enough to receive the error number specified in the RAISERROR statement after the error number is converted from the global variable, @@ERROR. The RAISERROR statement first displays the message level, state number, and the error message, Guru meditation error. The error number 99999 is then displayed separately using a PRINT statement.

Listing 14.23 Working with Errors in the Stored Procedure

declare @err char(5)
raiserror 99999 `Guru meditation error'
select @err=convert(char(5),@@ERROR)
print @err
go
Msg 99999, Level 16, State 1
Guru meditation error

(1 row(s) affected)

99999

You can also add your message text and an associated message number to the system table sysmessages. You use the system stored procedure, sp_addmessage, to add a message with a message identification number within the range 50,001 and 2,147,483,647. The syntax of the sp_addmessage system procedure is as follows:

sp_addmessage message_id, severity, message text' [, language [, {true | false} [, REPLACE]]]


CAUTION: If you enter a user-specified error number that has not been added to the sysmessages table and do not explicitly specify the message text, you'll receive an error that the message can't be located in the system table as shown in the following example:

raiserror (99999,7,2)
go
Msg 2758, Level 16, State 1
RAISERROR could not locate entry for error 99999 in Sysmessages.



User-defined error messages generated with a RAISERROR statement, but without a number in the sysmessages table return a message identification number of 50,000.

The severity level is used to indicate the degree or extent of the error condition encountered. Although severity levels can be assigned in the range of one through 25, you should usually assign your system message a severity level value from 11-16.

Severity levels of 11-16 are designed to be assigned through the sp_addmessages statement, and you can't assign a severity level of from 19-25 unless you're logged in as the administrator. Severity levels 17-19 are more severe software or hardware errors, which may not permit your subsequent statements to execute correctly.

Severity levels of 20-25 are severe errors and won't permit subsequent Transact-SQL statements to execute. System messages that have severity levels over nineteen can be problems, such as connection problems between a client system and the database server system or corrupted data in the database.


NOTE: Microsoft suggests that severe errors, those that have a severity level of nineteen or higher, should also notify the database administrator. The database administrator needs to know of these problems because such problems are likely to impact many different users and should be attended to as soon as possible.

When specifying messages, you enter an error message within single quotes of up to 255 characters. The remaining parameters of the sp_addmessage procedure are optional. The language parameter specifies one of the languages SQL Server was installed with. U.S. English is the default language if the parameter is omitted.

The next parameter, either True or False, controls whether the system message is automatically written to the Windows NT application event log. Use True to have the system message written to the event log. In addition, True results in the message being written to the SQL Server error log file.

The last parameter, REPLACE, is used to specify that you want to replace an existing user-defined message in the sysmessages table with a new entry.

Listing 14.24 shows the use of the sp_addmessage system stored procedure that adds a system message with an associated identification number and severity. A subsequent SELECT statement retrieves the message from the system table sysmessages. Finally, the RAISERROR statement is used to return the user-defined system message.

Listing 14.24 Using sp_addmessage

sp_addmessage 99999,13,'Guru meditation error'
go
select * from sysmessages where error=99999
go
raiserror (99999, 13,-1)
go
New message added.
error       severity dlevel description         languid
----------- -------- ------ ------------------ ------
99999       13       0      Guru meditation error    0

(1 row(s) affected)

Msg 99999, Level 13, State 1

Guru meditation error

You can use the system-stored procedure, sp_dropmessage, to remove a user-defined message from the system table sysmessages when it is no longer needed. The syntax of the sp_dropmessage is as follows:

sp_dropmessage [message_id [, language | `all']]

You're only required to enter the message number to drop the message. The two additional optional parameters permit you to specify the language from which the message should be dropped. You can use the keyword all to drop the user-defined message from all languages.

In the following example, a user-defined message in the default language of U.S. English is removed from the system table, sysmessages:

sp_dropmessage 99999
go
Message dropped.

Using WAITFOR

You use a WAITFOR statement to specify a time, a time interval, or an event for executing a statement, statement block, stored procedure, or transaction. The syntax of the WAITFOR statement is as follows:

WAITFOR {DELAY <`time'> | TIME <`time'> | ERROREXIT | PROCESSEXIT |  MIRROREXIT}

The meaning of each of the keywords that follow the WAITFOR keyword is shown in the following list:

In the following example of a WAITFOR statement, a DELAY is used to specify that a pause of forty seconds is taken before the subsequent SELECT statement is executed:

waitfor delay '00:00:40'
select * from employees

In the second WAITFOR example, a TIME is used to wait until 3:10:51 PM of the current day until the subsequent SELECT statement is executed.

waitfor time '15:10:51'
select * from employees

Using CASE Expressions

You can use a CASE expression to make an execution decision based on multiple options. Using the CASE construct, you can create a table that will be used to lookup the results you are testing and apply them to determine what course of action should be taken. The syntax of the CASE expression is as follows:

CASE [expression]
WHEN simple expression1|Boolean expression1 THEN expression1
[[WHEN simple expression2|Boolean expression2 THEN expression2] [...]]
     [ELSE expressionN]
END

If you use a comparison operator in an expression directly after the CASE keyword, the CASE expression is called a searched expression rather than a simple CASE expression. You can also use a Boolean operator in a searched CASE expression.

In a simple CASE expression, the expression directly after the CASE keyword always exactly matches a value after the WHEN keyword. In Listing 14.25, a CASE expression is used to substitute alternate values for the column department in the table company. In the following example, a CASE expression is used to return a corresponding set of alternate values for three department values of the table company.

Listing 14.25 Using CASE Expressions and Tests

select name,division=
case department
     when "Sales" then "Sales & Marketing"
     when "Field Service" then "Support Group"
     when "Logistics" then "Parts"
     else "Other department"
end,
badge
from company
go
name                 division          badge
-------------------- ----------------- -----------
Fred Sanders         Sales & Marketing 1051
Bob Smith            Sales & Marketing 1834
Mark McGuire         Support Group     1997
Stan Humphries       Support Group     3211
Sue Sommers          Parts             4411
Lance Finepoint      Other department  5522
Fred Stanhope        Support Group     6732
Ludmilla Valencia    Other department  7773
Jim Walker           Other department  7779
Jeffrey Vickers      Other department  8005
Barbara Lint         Support Group     8883
Sally Springer       Sales & Marketing 9998

(12 row(s) affected)

If you don't use an ELSE as part of the CASE expression, a NULL is returned for each non- matching entry, as shown in Listing 14.26:

Listing 14.26 Output from a Sample CASE Test

select name,division=
case department
when "Sales" then "Sales & Marketing"
when "Field Service" then "Support Group"
when "Logistics" then "Parts"
end,
badge
from company
go
name                 division          badge
----------------------------------------------
Fred Sanders         Sales & Marketing 1051
Bob Smith            Sales & Marketing 1834
Mark McGuire         Support Group     1997
Stan Humphries       Support Group     3211
Sue Sommers          Parts             4411
Lance Finepoint      (null)            5522
Fred Stanhope        Support Group     6732
Ludmilla Valencia    (null)            7773
Jim Walker           (null)            7779
Jeffrey Vickers      (null)            8005
Barbara Lint         Support Group     8883
Sally Springer       Sales & Marketing 9998

(12 row(s) affected)

You'll recall that a searched CASE expression can include comparison operators and the use of AND as well as OR between each Boolean expression to permit an alternate value to be returned for multiple values of the column of a table. Unlike a simple CASE expression, each WHEN clause is not restricted to exact matches of the values contained in the table column.

In Listing 14.27, comparison values are used in each WHEN clause to specify a range of values that are substituted by a single alternative value:

Listing 14.27 Using the WHEN Clause with CASE Statements

select "Hours Worked" =
case
when hours_worked < 40 then "Worked Insufficient Hours"
when hours_worked = 40 then "Worked Sufficient Hours"
when hours_worked > 60 then "Overworked"
else "Outside Range of Permissible Work"
end
from pays
go
Hours Worked
---------------------------------
Worked Sufficient Hours
Worked Sufficient Hours
Overworked
Worked Insufficient Hours
Overworked
Worked Sufficient Hours
Overworked
Worked Sufficient Hours
Outside Range of Permissible Work
Worked Insufficient Hours
Worked Sufficient Hours
Worked Sufficient Hours

(12 row(s) affected)


NOTE: When a CASE construct is executed, only the first matching solution is executed.


CAUTION: You must use compatible datatypes for the replacement expression of the THEN clause. If the replacement expression of a THEN clause is a datatype that is incompatible with the original expression, an error message is returned.

For example, a combination of original and replacement datatypes is compatible if the one is a variable length character datatype (VARCHAR) with a maximum length equal to the length of a fixed length character datatype (CHAR). In addition, if the two datatypes in the WHEN and THEN clauses are integer and decimal, the resultant datatype returned will be decimal in order to accommodate the whole and fractional portion of the numeric value.


You can also use both the COALESCE and NULLIF functions in a CASE expression. You use the COALESCE function to return a replacement value for any NULL or NOT NULL values that are present in, for example, the column of a database table. The syntax of one form of the COALESCE function is:

COALESCE (expression1, expression2)

In Listing 14.28, the COALESCE function is used to display either the product of hours_worked times rate or a zero if the columns hours_worked and rate are NULL:

Listing 14.28 Using the COALESCE Function

select badge, "Weekly Pay in Dollars"=coalesce(hours_worked*rate,0)
from pays2
go
badge       Weekly Pay in Dollars
----------- ---------------------
3211        400
6732        360
4411        520
5522        429
1997        510
9998        320
7773        550
8883        360
8005        420
7779        407
1834        400
1051        360
3467        0
3555        0
7774        0

(15 row(s) affected)


NOTE: A COALESCE function is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. An equivalent CASE expression to a COALESCE function is as follows:

CASE
     WHEN expression1 IS NOT NULL THEN expression1
     ELSE expression2
END

You can use a COALESCE function as part of a SELECT statement as an alternative way of returning an identical display or because you find the COALESCE function simpler to use.


You can also use a NULLIF function with or in place of a CASE expression. The NULLIF function uses the following syntax:

NULLIF (expression1, expression2)

In Listing 14.29, a simple SELECT statement is first used to display the table without using a NULLIF function to show all column values for all rows. A second SELECT statement is used to operate on the columns, badge, and old_badge.

Listing 14.29 Using the NULLIF Function

select * from company2
go
name                 department           badge       old_badge
-------------------- -------------------- ----------- -----------
Mark McGuire         Field Service        1997        (null)
Stan Humphries       Field Service        3211        (null)
Sue Sommers          Logistics            4411        (null)
Fred Stanhope        Field Service        6732        (null)
Ludmilla Valencia    Software             7773        (null)
Jim Walker           Unit Manager         7779        (null)
Jeffrey Vickers      Mailroom             8005        (null)
Fred Sanders         SALES                1051        1051
Bob Smith            SALES                1834        1834
Sally Springer       Sales                9998        9998
Barbara Lint         Field Service        8883        12
Lance Finepoint      Library              5522        13

(12 row(s) affected)

select name,nullif(old_badge,badge)
from company2
go
name
-------------------- -----------
Mark McGuire         (null)
Stan Humphries       (null)
Sue Sommers          (null)
Fred Stanhope        (null)
Ludmilla Valencia    (null)
Jim Walker           (null)
Jeffrey Vickers      (null)
Fred Sanders         (null)
Bob Smith            (null)
Sally Springer       (null)
Barbara Lint         12
Lance Finepoint      13

(12 row(s) affected)

The example only returns non-null values for rows that contain old_badge values that are different than new column values. In addition, a NULL is returned if no old column values were present. You can combine the use of the NULLIF and COALESCE functions to display the returned information in a more organized way.

Listing 14.30 combines a COALESCE and NULLIF function to return an old badge number only if it was different than the current badge number or if it was defined. If not, a new badge number is displayed.

Listing 14.30 Combining the COALESCE and NULLIF Functions

select name,badge=coalesce(nullif(old_badge,badge),badge)
from company2
go
name                 badge
-------------------- -----------
Mark McGuire         1997
Stan Humphries       3211
Sue Sommers          4411
Fred Stanhope        6732
Ludmilla Valencia    7773
Jim Walker           7779
Jeffrey Vickers      8005
Fred Sanders         1051
Bob Smith            1834
Sally Springer       9998
Barbara Lint         12
Lance Finepoint      13

(12 row(s) affected)

Reality Check

Stored procedures are nearly always the backbone of your system. You'll find that they make a good scaling point to move functionality from the client to the server. In cases where you find that you're repeating an SQL Server access over and over, consider moving it to a stored procedure and calling it from the application.

Perhaps one of the biggest benefits, of using stored procedures, for a software development house is the division of work between the development of the client application and the development of the server-side components. This was especially true in one case where an application was developed for an insurance company. Both the user interface and the database management were a challenge to implement.

By breaking development between the database and the client-side UI, it was possible to bring the project in on time, but still maintain experts in the development of the respective sides. The only thing, for example, that the UI development team knew about the database was the set of calls they needed to make (stored procedures) to get access to the information they needed. They didn't worry about the complicated search algorithms, the database management, or the rules implementations that were necessary behind the scenes.

On the other hand, the only thing the database team had to know about the user interaction with the application was the required response time and what the incoming information would look like. They didn't need to worry about what the dialog boxes looked like, or how the user set up the application.

If you think about it, you're breaking the development cycle into components as you do your application: client and server. You should use client-development experts for the tasks at which they are best--designing the interface, developing reports, and working with the users. Use the server-side developers for what they are best at--developing a solid database plan, implementing the server-side enforced rules, and so on. It provides you with real leverage on the personnel and project development cycles.

From Here...

In this chapter, you've seen how you can use Transact-SQL to control the flow of your SQL Server-based application. Remembering to use these techniques to manipulate information on the server can significantly improve performance of your application.

Here are some other areas of interest relating to the materials covered here:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.