Chapter 13 - Managing Stored Procedures and Using Flow-Control Statements

• What flow-control statements are available and how to use them - 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.

• How to work with a host language, returning information about the success or failure of your routine - Return codes or visual output to the user can increase the effectiveness of a stored procedure.

• How to work with variables within your procedures - Variables must be assigned a data type and a scope.

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'll be reviewing the logic and flow control statements that you have available to you in your SQL code.

It's important to keep in mind the client/server model when you're building your systems. Remember that 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 two different ends of the model to optimize the user's experience with your application.

Although SQL is defined as a non-procedural language, Microsoft 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 instead of writing programs using conventional programming language, such as C or Visual Basic, to perform operations with a SQL Server database and its tables.

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

• Stored procedures are compiled the first time that they're run and are stored in a system table of the current database. When they are compiled, they are optimized to select the best path to access information in the tables. This optimization takes into account the actual data patterns in the table, indexes that are available, table loading, and more. These compiled stored procedures can greatly enhance the performance of your system.

• Another benefit is that you can execute a stored procedure on either a local or remote SQL Server. This enables you to run processes on other machines and work with information across servers, not just local databases.

• An application program written in a language, such as C or Visual Basic, can also execute stored procedures, providing an optimum "working together" solution between the client side software and SQL Server.

Defining Stored Procedures

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

In the following example, a simple procedure is created that contains a SELECT statement to display all rows of a table. Once the procedure is created, its name is simply entered on a line to execute the procedure. If you precede the name of a stored procedure with other statements, you use the EXECUTE procedure-name statement to execute the procedure.

create procedure all_employees
as select * from employees

all_employees

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

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.

Stored procedures are treated like all other objects in the database. Therefore, they are subject to all of the same naming conventions and other limitations.

Using Parameters with 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 the following example, 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.

When a procedure executed 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.

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 by language.

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)

In the following example, two procedures are created with the same name, but as version one and two and then subsequently executed once both are defined. Both procedures use a PRINT statement to return a message that identifies the procedure version. When the procedure is executed without the version number specified, the first version of the procedure is executed.

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

You can use the SET NOEXEC ON command the first time that you execute a procedure to check it for errors, rather than 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 13.1 shows the Manage Stored Procedures dialog box before any statements are typed into the dialog box.

   Fig. 13.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 13.2 shows a simple Transact-SQL statement and a new procedure name entered in the Manage Stored Procedures dialog box.

   Fig. 13.2 - Click the Procedures list box to display a list of the procedures in the selected database.

Displaying and Editing Procedures

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

sp_helptext proc3

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

(1 row(s) affected)

sp_help proc3

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

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)

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 brought up.

You actually can't directly edit stored procedures. You'll notice that in the Manage Stored Procedures dialog box (refer to figures 13.1 and 13.2) that is brought up for an existing procedures has additional Transact-SQL statements added previous to the procedure definition. The conditional IF statement is used to check if the procedure is already defined (which it will be if you were able to select it) and delete the procedure. The old procedure definition must be removed and a new procedure, with the name you specify, will be substituted for the old procedure.

Dropping Procedures

DROP PROCEDURE procedure_name_1, ...,procedure_name_n

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

I created a procedure in a previous session, but I was unable to find the procedure again in a subsequent session.

Procedures are defined within a database. In your subsequent session, you probably found yourself in a different database than the database in which the procedure was originally defined. You can ask the database administrator to define the database that your procedure was defined in to your default database and you'll always be positioned to it each time you begin a session. You can also enter the USE command followed by the name of the database in which your procedure was defined to position yourself to it so that you can locate the procedure.

Understanding Procedure Resolution and Compilation

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.

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

• A procedure is always recompiled when SQL Server is stated (usually after a reboot of the underlying operating system), and the procedures are first executed.

  
  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 may never be stopped and restarted unless a major error occurs, a hardware malfunction, or an update to a new version of SQL Server or Windows NT. The recompilation of stored procedures would not be done frequently on systems that run non-stop.
  

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

• A procedure's execution plan is also automatically recompiled whenever an index on a table referenced in the procedure is dropped. A new execution plan must be compiled because the current once references an object, the index, for the retrieval of the rows of a table that doesn't exist. The execution plan must be redone to permit the queries of the procedure to be performed.

• Compilation of the execution plan is also re-initialized if the execution plan in the cache is currently in use by another user. A second copy of the execution plan is created for the second user. If the first copy of the execution plan weren't in use, it could have been used rather than a new execution plan being created. When a user finishes executing a procedure, the execution plan is available in the cache for reuse by another user with appropriate permissions..

• A procedure is also automatically recompiled if the procedure is dropped and re-created. All copies of the execution plan in the cache are removed because the new procedure may be substantially different from the older version and a new execution plan is necessary.

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 either 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 its 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 redone automatically. 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

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. Because—if you create a procedure with the RECOMPILE option—the procedure is recompiled each time the procedure is executed, 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's executed during replication.

See the Chapter 20 section entitled "Setting Up and Managing Replication"

You can add the ENCRYPTION option to a CREATE PROCEDURE statement to encrypt the definition 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.

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

sp_makestartup procedure_name

In the following example, 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 procedure is removed from automatic execution at start up.

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

• Procedures

• Rules

• Defaults

• Triggers

• Views

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 or change the database or query environment 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 also can't drop an object and reference or re-create 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 with 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

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 the following example of an interactive ISQL session, the GO command is used first to cause the execution of the USE command and then to signal the end of the second batch, two SELECT statements.

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)

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

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)

Using Flow-Control Statements

Using IF...ELSE

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

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 upon 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

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

No entry

In the following example, our previous examples are rewritten to use an IF and ELSE clause. 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'

Using BEGIN...END

BEGIN
      statements
END

In the following example, 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 a IF clause, no ELSE clause is part of the 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)

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)

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

WHILE
     <boolean_expression>
     <sql_statement>

In the following example, 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 incremented. The statements associated with the WHILE execute until the variable x reaches a value of five.

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)

Using BREAK

   WHILE
     <boolean_expression>
     <sql_statement>
   BREAK
     <sql_statement>

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

WHILE
  <boolean_expression>
  <statement>
BREAK
  <statement>
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

In the following example, 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.

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

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)

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)
...

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.

Defining and Using Variables

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 a 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...]

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

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

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

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

Table 13.1 lists the names of all Microsoft SQL Server global variables and a brief description of the information that's contained within them.

Table 13.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 the following example, 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.

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

SQL Server for Windows NT 4.20 (Intel X86)
       Aug 24 1993 00:00:00

(1 row(s) affected)

Server name is BOB486

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

label:

GOTO label

The following example shows the use of the GOTO statement that is used to transfer 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.

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:

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 13.2 shows several of the return status values that are reserved by SQL Server.

Table 13.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 that 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

The following example 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.

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.

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.

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 the following example, 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.

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 the following example, the returned value is checked as part of a conditional statement and a message 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.

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

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:

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 the following example, 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.

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]]]

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 that are generated with a RAISERROR statement 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 such problems as connection problems between a client system and the database server system or corrupted data in the database.

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.

The following example 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.

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:

• DELAY—Specifies an interval or time to elapse

• TIME—A specified time (no date portion) of up to 24 hours

• ERROREXIT—Until a process terminates abnormally

• PROCESSEXIT—Until a process terminates normally or abnormally

• MIRROREXIT—Until a mirrored device fails

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 a 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 the following example, 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.

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 the following example.

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 the following example, comparison values are used in each WHEN clause to specify a range of values that are substituted by a single alternative value.

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)

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

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 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 the following example, 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.

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)

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 simply 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 the following example, 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.

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.

The following example 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. Otherwise, a new badge number is displayed.

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)

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 for your application.

Here are some other areas of interest that relate to the materials you've been working with here:

• Chapter 4, "Creating Devices, Databases, and Transaction Logs" teach you how to create and use user-defined datatypes.

• Chapter 11, "Managing and Using Rules and Defaults" teaches you how to create and use rules and defaults.

• Chapter 14, "Creating and Managing Triggers" teaches you how to use triggers to maintain referential integrity in the database.

• Chapter 17, "Optimizing Performance" teaches you ways to optimize the operation of Microsoft SQL Server, including the correct sizing of the procedure cache.

---

QUE Home Page

For technical support For our books And software contact support@mcp.com

Copyright © 1996, Que Corporation

---

Table of Contents

12 - Understanding Transactions and Locking

14 - Creating and Managing Triggers