Programming Constructs

The languages that interface to database management systems are sometimes divided into three categories:

  • DML, Data Manipulation Language?This includes the ability to read and manipulate the data. Examples are SELECT, INSERT, DELETE, and UPDATE.

  • DDL, Data Definition Language?Creating and altering the storage structures; an example is CREATE TABLE.

  • DCL, Data Control Language?Defining permissions for data access; examples are GRANT, REVOKE, and DENY.

T-SQL includes other statements that can be useful, for instance, in tying together the DML statements in a stored procedure, such as IF, ELSE, and WHILE.

The IF Statement

The IF statement takes one argument: boolean_expression, which is an expression that can evaluate to TRUE or FALSE. The code to be conditionally executed is a statement block:

IF boolean_expression 

You define a statement block with the statements BEGIN and END. If no statement block is defined, only the first statement following the IF statement is executed when the boolean_expression is true.

In Listing 26.11, a script checks for the existence of a table, prints a message if the table exists, and, if it does, drops the table.

Listing 26.11 Using the IF Statement to Perform Conditional Processing
IF OBJECTPROPERTY(OBJECT_ID('orders'), 'istable') = 1
    PRINT "Dropping orders Table"
    DROP TABLE orders
    PRINT "Table orders does not exist"


The WHILE statement allows you to loop while an expression evaluates to true. The syntax for WHILE is as follows:

WHILE boolean_expression 

The statement block contains the BREAK or the CONTINUE statements. BREAK exits the WHILE loop, and CONTINUE skips any remaining statements in the statement block and evaluates the boolean_expression again. Listing 26.12 demonstrates a simple example of using a WHILE loop to repeatedly update the titles table until the average price of all books exceeds $25.

Listing 26.12 Using the WHILE Loop for Iterative Processing
/* loop until average price equals or exceeds $25*/
while (select avg (price) from titles) < $25
    update titles set price = price * 1.05
    /* if more than 10 books are less than
    ** $15, continue processing */
    if (select count(*) from titles
           where price < $15) > 1
    /* If maximum price of any book exceeds
    ** $50, exit loop */
    if (select max(price) from titles) > $50

CASE Expression

The CASE expression is an ANSI SQL-92 construct that allows for expressions that can evaluate a number of conditions and return a single result. CASE expressions are allowed wherever constant expressions are allowed, such as the SELECT list, the WHERE clause, GROUP BY, and ORDER BY. The CASE expression has two forms of syntax. The simple CASE expression compares an expression to a set of values to determine the result:

case expression 
     when value1 then result1
     [when value2 then result2]
     [else resultN]

The searched CASE expression evaluates a set of Boolean conditions to determine the result:

    when Boolean_expression1 then expression1
    [[when Boolean_expression2 then expression2] [...]]
    [else expressionN]

CASE expressions are an excellent tool to replace data values in columns (for example, code columns) with more meaningful values within your resultset, as shown in Listing 26.13.

Listing 26.13 Using the CASE Expression to Substitute for Column Values
select substring (title, 1, 20) as Title,
       case type
          when 'popular_comp' then ' Computer book'
          when 'mod_cook'  then ' Cookbook'
          when 'trad_cook'  then ' Cookbook'
          else 'Other book'
       end as Type,
       'Sales are ' +
          when ytd_sales < 5000 then 'Poor'
          when ytd_sales between 5001 and 10000 then 'Good'
          when ytd_sales > 10000 then 'Awesome'
          else 'Unknown'
       end as Sales
   from titles

Title                 Type             Sales
--------------------  ---------------- --------------------
The Busy Executive's  Other book       Sales are Poor
Cooking with Compute  Other book       Sales are Poor
You Can Combat Compu  Other book       Sales are Awesome
Straight Talk About   Other book       Sales are Poor
Silicon Valley Gastr  Cookbook         Sales are Poor
The Gourmet Microwav  Cookbook         Sales are Awesome
The Psychology of     Other book       Sales are unknown
But Is It User Frien  Computer book    Sales are Good
Secrets of Silicon    Computer book    Sales are Poor
Net Etiquette         Computer book    Sales are unknown
Computer Phobic AND   Other book       Sales are Poor
Is Anger the Enemy?   Other book       Sales are Poor
Life Without Fear     Other book       Sales are Poor
Prolonged Data Depri  Other book       Sales are Poor
Emotional Security:   Other book       Sales are Poor
Onions, Leeks, and    Cookbook         Sales are Poor
Fifty Years in Bucki  Cookbook         Sales are Awesome
Sushi, Anyone?        Cookbook         Sales are Poor

The CASE expression can even be used in the SET clause of an UPDATE statement to conditionally set a value based on column values within the row, avoiding the need to use a cursor:

update titles 
   set price = case when ytd_sales < 500 then price *.75
                    when price > $15 then price * .90
                    else price * 1.15


RETURN is used to stop execution of a batch, stored procedure, or trigger. When used in a stored procedure, RETURN can take an integer as an argument. The value 0 indicates successful execution. The values -1 to -99 are reserved by Microsoft (currently, -1 to -14 are in use), so you should use values outside that range.


You might want to set up some standard return values for your stored procedures. One option is to have different return values for the type of statement that caused the error return, such as 1 when a SELECT statement fails, 2 when an UPDATE statement fails, 3 when a DELETE statement fails, 4 when an INSERT statement fails, and so on.

Another approach is to have a different return value for each statement in the procedure that can lead to a return. For example, the first statement would return -101 on failure, the second statement would return -102 on failure, the third statement would return -103 on failure, and so on. The benefit of this approach is that if you capture the return status from the stored procedure, you can identify exactly which statement caused the error that led to the return.


GOTO (yes, there is a GOTO statement in T-SQL) branches to a defined label. GOTO can be useful for error handling in stored procedures, for example. The following is a code fragment from a stored procedure that checks for errors after each statement and exits the procedure with a return code if an error occurs:

[View full width]
BEGIN TRAN INSERT orders(customer_number) VALUES(1) IF @@ERROR <> 0 GOTO err_handle RETURN 0 /* ... */ err_handle: RAISERROR ('An error occurred in the stored procedure. The transaction has been rolled graphics/ccc.gifback', 12, 1) ROLLBACK TRANSACTION RETURN -101


You can use WAITFOR to suspend execution for a specified delay (WAITFOR DELAY) or until a specified time (WAITFOR TIME). Say, for example, you want to generate a deadlock. (For instance, you might have defined an alert for a deadlock error and you want to test it.) You must be able to start execution of both batches more or less simultaneously for the deadlock to occur. This is hard to do in real time, so you can introduce a wait for 10 seconds in the first transaction to give you time to initiate the second transaction.

To try this out yourself, open up two separate connections to the same SQL Server in Query Analyzer. In one connection, execute the following code to initiate one transaction:

use pubs 
UPDATE authors SET au_lname = au_lname
WAITFOR DELAY '00:00:10'
UPDATE titles SET title = title

Quickly switch to the other connection, and execute the following code to initiate another transaction:

use pubs 
UPDATE titles SET title = title
UPDATE authors SET au_lname = au_lname

If you time it correctly, this should lead to a deadlock once the WAITFOR DELAY expires in the first connection and the second update statement is executed.


The EXEC (or EXECUTE) command is used as a keyword for executing stored procedures. Introduced in version 6.0, EXEC also gives you the ability to execute strings and variables containing strings, which can be useful.

Perhaps you want to write a procedure to perform UPDATE STATISTICS for all tables in the database without having to hardcode the table names; that way, if you add or remove tables from the database, you don't have to edit the procedure. Unfortunately, the UPDATE STATISTICS command does not accept a variable as an argument. However, you can build the command dynamically and execute the command using the EXEC statement, as shown in Listing 26.14.

Listing 26.14 Dynamic SQL Execution Using the EXEC Statement
DECLARE c1 cursor
   for select table_name from information_schema.tables
    where TABLE_TYPE = 'Base Table'
declare @tbl_name NVARCHAR(128)
open c1
fetch c1 into @tbl_name
while @@fetch_status = 0
    EXEC('UPDATE STATISTICS ' + @tbl_name)
    fetch c1 into @tbl_name
close c1
deallocate c1

Another example is if you want to write a stored procedure that will SELECT rows from a table name passed to it as an argument. SQL Server does not accept variables for table names, column names, and so on, so the following syntax will produce an error message:

SELECT * FROM @tbl_name 

To dynamically retrieve data from a table name passed in as a parameter, you can build a dynamic query to be executed by the EXEC command:

CREATE PROC general_select @tbl_name NVARCHAR(128) AS 
    EXEC('SELECT * FROM ' + @tbl_name)
EXEC general_select authors

For more information on executing dynamic SQL within stored procedures, see Chapter 28.


A batch is simply a set of commands sent to SQL Server for execution. Do not confuse the batch term as used here with traditional batch processing, in which mass modifications are performed, often at low-activity periods.

Basically, SQL Server receives a string (containing T-SQL commands) from the client application. SQL Server parses this string as a unit, searching for keywords. If a syntax error is found, none of the statements in the batch are executed, and an error message is returned to the client application.

In Query Analyzer, ISQL, and OSQL, the string GO is used to separate batches. When the tool finds the string GO, it takes all text up to the preceding GO and submits it to SQL Server for execution.

Some restrictions for batches concern what commands can be combined with other commands within a batch. Some examples follow:

  • You cannot combine certain commands within a batch. Most CREATE commands must be executed in a single batch. The exceptions are CREATE TABLE, CREATE INDEX, and CREATE DATABASE.

  • When calling a stored procedure, you must precede the procedure name with EXECUTE if it's not the first statement in a batch. If SQL Server doesn't recognize the first statement in a batch, it simply assumes that the string is a call to a stored procedure.

A related concept is the SQL script. A SQL script is a text file containing one or more batches. Scripts are often used with Query Analyzer, ISQL, and OSQL. You do not have to specify GO after the last command in a script file; the tools will automatically generate an end-of-batch signal.

Listing 26.15 creates a table and a view. Note that the CREATE commands are separated by GO.

Listing 26.15 Creating a Table and a View That Only Display Recent Orders
    stor_id char(4) REFERENCES stores(stor_id),
CREATE VIEW recent_orders AS
   SELECT order_number, stor_id, order_date
      FROM orders
      WHERE order_date > GETDATE() ? 14


Anyone who has ever had to review or change some code recognizes the importance of comments. Even if it seems obvious what the code does when you're writing it, the meaning will most certainly not be as obvious later, especially if someone else other than the original author is looking at it.

When SQL Server finds a comment, it does not execute anything until the end of the comment. The Query Analyzer's syntax coloring indicates commented text with a green color by default. SQL Server supports two types of comment markers:

/* Comments */ 

These comment markers are useful for commenting several lines. None of the text between the comment markers is parsed, compiled, or executed. For shorter comments, you can use

-- Comments 

SQL Server will not execute any of the text following the markers up to the end-of-line. The -- comment markers are defined in ANSI SQL-92.

Following is an example of a batch with an opening comment block that describes what the batch performs and a comment line later in the code that can be altered for debugging purposes:

/* Retrieves all orders that have been submitted the last day. 
The SELECT COUNT is only for debugging purposes */
SELECT order_number, stor_id, order_date
   FROM orders
   WHERE order_date > GETDATE() -1
--SELECT 'Number of orders returned':, @@ROWCOUNT

Both types of comments can be nested within a /*...*/ comment block. Comment blocks are local to a batch and cannot span across multiple batches?in other words, you cannot specify the end-of-batch (GO) separator within a /*...*/ comment block. The example in Listing 26.16 attempts to use a comment block to comment out the GO command. Notice the error messages generated.

Listing 26.16 Attempting to Comment Out the GO Command
/* this is a comment
select * from titles
select * from publishers

Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '*'.

Local Variables

Local variables allow you to store values temporarily. The variable is always declared as a certain datatype with the DECLARE statement. The datatype can either be system supplied or user defined. The variable's name always begins with the @ sign and can be up to 128 characters in length.

Local variables initially are set to NULL. The variable is assigned a value with the SELECT statement or the SET statement. The SET statement can only set one variable at a time. A SELECT statement can assign values to one or more variables at a time and it is more efficient to perform multiple assignments in single select rather than performing multiple SELECT or SET statements. Also, a SELECT statement used to assign values to one or more local variables, referred to as an assignment select, cannot also return result rows.

Listing 26.17 prints the number of distinct book types in the titles table. You declare a local variable, assign it a value, and then print the contents of the variable.

Listing 26.17 Assigning a Value to a Local Variable and Printing Its Contents
DECLARE @user_msg VARCHAR(255)
SELECT @user_msg = 'There are ' + CONVERT(VARCHAR(3),
       (SELECT COUNT(DISTINCT type) FROM titles))
        + ' book types in the titles table.'
PRINT @user_msg

There are 6 book types in the titles table.


The life span of a local variable is the batch, trigger, or stored procedure in which it is declared. After any one of these has completed processing, the variable ceases to exist. The scope of a local variable is limited to the batch, trigger, or stored procedure in which it is declared. In other words, a local variable declared in one procedure cannot be accessed by a procedure called within that procedure unless it is passed as a parameter.

If you want to store a value that persists between batches in T-SQL, or is accessible by a called procedure, you must create a temporary table in which to store the value.

Local variables, except for table variables, are scalar variables and can only hold a single value at a time. If an assignment select statement returns multiple rows, the local variable will contain the value from the last row returned. If the assignment select returns no rows, the local variable is not set to NULL, but it retains the value it had prior to the assignment select.

Local variables are often used in stored procedures.

Functions That Used to Be Called Global Variables

A certain set of functions used to be called global variables in earlier releases of SQL Server. The name global was apparently confusing to some, implying that the scope of the variable was beyond that of a local variable. Global variables were apparently mistaken by some as variables that a user could declare and the scope of the variable would extend across batches, which is not the case. You can name a variable starting with two or more at signs (@@), but it will still behave as a local variable.

These global variables, now called functions, contain information that SQL Server maintains. They exist so that an application can check things such as the error code for the last executed command. Microsoft online books categorizes functions in the various categories of the SQL Server functions. However, the documentation somewhat blurs the lines between session-level and server-level functions the way that Microsoft groups them. For this reason, this chapter categorizes them as connection-specific, monitoring-related, and general functions.

These functions are useful because some of them contain information that cannot be found elsewhere or would be difficult to obtain with other means.

For the connection-specific functions outlined in Table 26.14, SQL Server maintains separate values for each connection.

Table 26.14. Connection-Specific Functions
Function Name Returned Value
@@CURSOR_ROWS Number of rows populated in the last opened cursor within the connection.
@@DATEFIRST The first day of the week. (7 is Sunday, 1 is Monday, and so on. Set with SET DATEFIRST.)
@@ERROR The error number generated by the last executed command within the session. This is valuable for error checking in stored procedures, batches, and triggers.
@@FETCH_STATUS Indication of whether the last fetch operation from a cursor within the session was successful.
@@IDENTITY The identity value generated by the last insert statement within the session. The @@IDENTITY value is unaffected by other connections' inserts. To obtain information about the last identity value inserted by any session, use the IDENT_CURRENT('tablename') function described previously in this chapter.
@@LOCK_TIMEOUT The lock wait timeout value in milliseconds (set with SET LOCK_TIMEOUT).
@@LANGID The connection's language ID in use.
@@LANGUAGE The connection's language in use; a character string. Set by SET LANGUAGE.
@@NESTLEVEL The current nesting level for a stored procedure or trigger. This is important to check in recursive procedures to ensure the nesting level doesn't exceed the maximum of 32.
@@PROCID The ID of the currently executing stored procedure.
@@REMSERVER The name of the remote SQL Server from which a remote procedure call was invoked.
@@ROWCOUNT The number of rows affected (modified or read) by the last command executed within a session.
@@SPID The current connection ID.
@@TEXTSIZE The maximum number of bytes returned by a SELECT statement when reading text and image data (set by SET TEXTSIZE). Note that this can be further limited by the client application.
@@TRANCOUNT The current transaction nesting level. See Chapter 31, "Transaction Management and the Transaction Log" to learn more about this function.
@@ERROR The error number of the previously executed command within a session. Useful for error handling in stored procedures and triggers.

The following code fragment demonstrates using the @@ERROR function to check for errors after each statement and branching to an error-handling routine if an error occurs:

INSERT orders(customer_number) VALUES(1)
 IF @@ERROR <> 0 GOTO err_handle
/* ... */
RAISERROR ('An error occurred in the stored procedure.
The transaction has been rolled back', 12, 1)

Listing 26.18 demonstrates using the @@IDENTITY function. In this example you need to find out the identity value generated by the insert into customers so it can be used in the insert into the orders table. You can use the @@IDENTITY function to capture this. Note that you need to save the value returned from @@IDENTITY into a local variable if you need access to it after any subsequent INSERT statements. All INSERT statements update @@IDENTITY, even those that insert into a table without an identity column (if there is no identity column on the table, the insert will set @@IDENTITY to NULL). You do not have to worry about other connections' inserts affecting the @@IDENTITY value because @@IDENTITY is maintained per connection.

Listing 26.18 Using the @@IDENTITY Function to Get the Last Generated Identity Value
CREATE TABLE customers
   customer_name NVARCHAR(100) NOT NULL,
   customer_comments NVARCHAR(1000) NULL)

   customer_number INT REFERENCES customers(customer_id),

DECLARE @cust_id INT
INSERT customers (customer_name, customer_comments)
    VALUES ('Hardware Suppliers AB', 'Stephanie is contact.')
SELECT @cust_id = @@IDENTITY
INSERT orders (customer_number)
    VALUES (@cust_id)


Be careful if an insert trigger exists on a table and the insert trigger performs an insert into another table that also has an identity column defined on it. The @@IDENTITY value returned will be from the table inserted into by the trigger, not the original insert. You can use the scope_identity() function instead of @@IDENTITY to return the identity value generated by the original insert statement.

The monitoring-related functions are listed in Table 26.15. These functions are rarely used and are included here for completeness. Typically, DBCC SQLPERF and SQL Performance Monitor give similar information in a more useful fashion.

Table 26.15. Monitoring-Related Functions
Function Name Returned Value
@@CONNECTIONS The number of login attempts since the last restart of SQL Server.
@@CPU_BUSY The number of time ticks (currently 1/100 second) that the machine's CPU has been performing SQL Server work since the last restart of SQL Server.
@@IDLE The number of time ticks (currently 1/100 second) that the machine's SQL Server has been idle since the last restart of SQL Server.
@@IO_BUSY The number of time ticks (currently 1/100 second) that SQL Server has been performing I/O operations since the last restart of SQL Server.
@@PACK_RECEIVED The number of packets received by SQL Server since the last restart of SQL Server.
@@PACK_SENT The number of packets sent by SQL Server since the last restart of SQL Server.
@@PACKET_ERRORS The number of times that an error occurred while sending a packet since the last restart of SQL Server.
@@TOTAL_ERRORS The number of times that an error occurred while reading or writing since the last restart of SQL Server.
@@TOTAL_READ The total number of physical reads since the last restart of SQL Server.
@@TOTAL_WRITE The total number of physical writes since the last restart of SQL Server.

Outlined in Table 26.16, the configuration functions provide information about the current configuration option settings, which are useful for administrative purposes. The most useful one is @@VERSION, which returns the version number and the service pack level.

Table 26.16. Configuration Functions
Function Name Returned Value
@@DBTS The current database's last-used timestamp value. A new timestamp value is generated whenever a row with a timestamp column is updated or inserted.
@@MAX_CONNECTIONS The maximum number of user connections that the installation can support. @@MAX_CONNECTIONS does not reflect the currently configured value of user connections.
@@MAX_PRECISION The maximum precision value for decimal and numeric datatypes.
@@MICROSOFTVERSION A Microsoft internal version number. This should not be used for version checking and handling. Use @@VERSION instead.
@@PROCID The ID of the currently executing stored procedure.
@@SERVERNAME The name of the SQL Server. This should match the machine name; if it doesn't, you might want to drop the old (wrong name) with sp_dropserver and add the new (correct name) with sp_addserver.
@@SERVICENAME The registry key name that SQL Server is running under. Returns MSSQLServer if the current instance is the default; otherwise, the instance name of the current instance is a named instance.
@@TIMETICKS The number of microseconds per time tick.
@@VERSION The SQL Server version number.

Listing 26.19 shows how you can use @@VERSION to check the version number of the SQL Server. The @@VERSION function provides the SQL Server version along with the build number and code freeze date of the currently running SQL Server executable. The last three digits after the second decimal point tell the build number, which indicates which service pack you are running. Unfortunately, no standard associates build numbers with service packs. (The service pack number listed on the last line is the service pack of the operating system you are running.) The only way to know which service pack of SQL Server you are running is to check the ReadMe file that comes with the service packs. The following are the current build numbers for SQL Server 2000:

  • 194?Initial Release version of SQL Server 2000

  • 384?Service Pack 1

  • 534?Service Pack 2

Listing 26.19 Using the @@VERSION Function to Determine the Version of SQL Server

Microsoft SQL Server  2000 - 8.00.534 (Intel X86)
        Nov 19 2001 13:23:50
        Copyright (c) 1988-2000 Microsoft Corporation
        Developer Edition on Windows NT 5.1 (Build 2600: )

Another way of determining the version of SQL Server is to use the new SERVERPROPERTY() system function and check the productversion or productlevel server properties:

select cast (serverproperty('productversion') as varchar(20)) AS 'Product Version' 
select cast (serverproperty('productlevel') as varchar(10)) AS 'Product Level'

Product Version

Product Level

The advantage of using the new SERVERPROPERTY() system function is obvious?no more looking up the build number in the ReadMe file to determine the service pack applied!

Returning Messages from T-SQL

SQL Server provides two methods of returning messages from within Transact-SQL code: PRINT and RAISERROR. The way that messages are handled and displayed by a client application is determined by the database API that the application uses. For example, DB-Library registers two callback handlers: an error handler and a message handler. The message handler is called when messages are sent via the PRINT statement. The error handler is called when messages are sent via the RAISERROR command. For ODBC applications, the SQLError function handles the results of PRINT and RAISERROR.


The PRINT statement is used to pass a message string to the client program. The message string can be a fixed string, local variable or function, or any string expression up to 8,000 characters in length. Anything beyond 8,000 characters is truncated. The syntax is as follows:

print {'character_string' | @local_variable | @@function | string_expr} 

The following example displays the current time from SQL Server:

print 'The current time is: ' + convert(char(8), getdate(), 108) 

Messages returned by the PRINT command return a severity of 0. PRINT is commonly used in SQL script files to print information to the output file.


Although SQL Server generates most error messages automatically, sometimes you will need to communicate to a client that some type of error has occurred within a SQL batch, trigger, or stored procedure. You can generate a message with the RAISERROR command. The RAISERROR command has the following syntax:

RAISERROR([err_no]|[err_string], severity, state [, argument[, ...]]) 
           [WITH option[, ...]]

If you supply an error string, the error number will always be 50,000. If you supply an error number, that error number and corresponding message must be defined in the sysmessages table in the master database.

The severity level indicates the type of problem encountered. Typically, the higher the severity level, the more serious the error. Severity levels higher than 19 are considered fatal errors and cause the process to terminate its connection to SQL Server. Only system administrators can raise a severity level of 19 or higher and they must specify the WITH LOG option. Typically, user-generated error messages will use only levels 10 or 16.


If you specify a severity of 10 or less, RAISERROR returns the message string similar to the way a print statement is returned. No error number is associated with it and some APIs will not treat it as an error message. For example, examine the difference between the following two error messages, one with severity 16 and one with severity 10:

RAISERROR ('Help, I've fallen and I can't get up', 16, 1) 
select @@error as 'Error Number'
RAISERROR ('Hello world', 10, 1)
select @@error as 'Error Number'
Server: Msg 50000, Level 16, State 1, Line 1
Help, I've fallen and I can't get up

Error Number

Hello world
Error Number

Table 26.17 presents the severity levels currently defined in SQL Server.

Table 26.17. Descriptions of Severity Levels
Severity Level Description
0 or 10 Informational status messages
11?16 Non-fatal, user-correctable errors (for example, invalid object name)
17 Insufficient resources; for example, out of locks or disk space
18 Internal errors, non-fatal
19 Resource problems, fatal
20 Fatal error in current process
21 Fatal error in database process
22 Fatal error, table integrity suspect
23 Fatal error, database integrity suspect
24 Fatal error, hardware error

The state parameter can be any value from 1?127. For SQL Server?generated error messages, the value of state indicates where in the SQL Server code the error came from. This information might be useful for Microsoft Product Support. For user-defined error messages, the state really has no meaning. You could use state to indicate the line in the SQL code that generated the error. Another situation in which you might find the state parameter useful is when you execute a script using ISQL or OSQL. If you execute the RAISERROR with a state of 127, the processing of the script file terminates. Suppose you have a simple batch file that executes the following:

ISQL /Usa /P /iMyBatch.SQL /n 

and the script file (MyBatch.SQL) contains the code in Listing 26.20.

Listing 26.20 Using State 127 to Terminate a Batch Processed with ISQL or OSQL
-- Exit if users connected to database.
IF (SELECT COUNT(*) FROM master..sysprocesses
    WHERE dbid = DB_ID('pubs')) > 0
RAISERROR ('Cannot proceed with batch, users connected to database.', 16, 127)
-- If not, continue with whatever you want to do
SELECT au_fname, au_lname FROM pubs..authors

If the IF statement evaluates to true, the RAISERROR statement will terminate the processing of the script file. This is not the same result that you get from issuing a RETURN statement. The RETURN statement would have terminated the batch but executed the remaining batches in the script file.

The arguments are used to insert data (table name or other information stored in local variables) into the message string. A maximum of 20 arguments can be specified. In the message string, you need to specify placeholders where the arguments are to be substituted. The placeholders are similar to the ones used in the C language printf command and are listed in Table 26.18.

Table 26.18. Placeholder Arguments for RAISERROR
Argument Datatype
d or I Signed integer
o Unsigned octal
p Pointer
s String
u Unsigned integer
x or X Unsigned hexadecimal

The following is an example of using RAISERROR with arguments:

declare @count int, 
       @table varchar(128)
select @table = 'titles', @count = count(*) from titles
RAISERROR ('There are %d row(s) in the %s table', 10, 1, @count, @table)

There are 18 row(s) in the titles table

The available options for RAISERROR include the following:

LOG? The message is sent to SQL Server's error log and NT's event log. Only a system administrator can specify this option.

NOWAIT? The message is sent directly to the client. This is useful for long-running operations to return information without waiting until the batch completes, such as to allow an application to display a status indicator.

SETERROR? The message forces the actual error number to be returned to the function @@ERROR. This is useful when the severity is lower than 11.


To reduce network traffic, SQL Server typically waits until either the end of a batch or the connection output buffer fills up before returning results or messages to a client application. At times, if you have a long-running SQL batch that possibly contains a loop and you want to return messages or resultsets as they are generated, use the RAISERROR command with the NOWAIT option. The NOWAIT option will cause the current contents of the output buffer to be immediately flushed back to the client application. Any severity less than 11 will be treated similarly to a print statement.

Managing SQL Server Error Messages

SQL Server error messages are stored in the sysmessages table in the master database. Table 26.19 describes the columns in the sysmessages table.

Table 26.19. Columns in the sysmessages Table
Column Name Description
error The error number. Every error message has a unique error number.
severity The severity level. A higher severity level generally indicates a more severe problem. SQL Server will terminate the connection and perform a rollback (if a transaction was started) for severity levels greater than 19.
dlevel For internal use.
description The message string with placeholders.
mslangid System message group ID.

User-defined error messages must have an error number that is greater than 50,000. The maximum value for an error number is 2,147,483,647.

You can also add your own error messages, which can be useful for centralizing error reporting from your application. Chapter 18, "SQL Server Scheduling and Notification," describes how to add messages in Enterprise Manager. You can also manage messages with the stored procedures sp_addmessage, sp_dropmessage, and sp_altermessage. The error number must be greater than 50,000. For more information on these commands, refer to Chapter 18.

Listing 26.21 adds a user-defined message and calls it from T-SQL code.

Listing 26.21 Adding an Error Message to SQL Server and Generating the Error
[View full width]
sp_addmessage 50001, 16, 'The row(s) from table %s could not be deleted. There are rows 
graphics/ccc.gifin table %s that refer to this row. Delete those rows first.'

RAISERROR (50001, 16, 1, 'Titles', 'Titleauthor')

Server: Msg 50001, Level 16, State 42000
The row(s) from table Titles could not be deleted. There are rows in table Titleauthor 
graphics/ccc.gifthat refer to this row. Delete those rows first.

The FORMATMESSAGE function can be used to return a message from the sysmessages table. Its typical use is to substitute arguments into the message string and construct the message as it would be returned by RAISERROR. Unlike RAISERROR, which prints the message immediately, FORMATMESSAGE returns the constructed message for further processing.

The syntax for FORMATMESSAGE is as follows:

FORMATMESSAGE ( msg_number , argument [ ,...n ] ) 

The following example uses FORMATMESSAGE with the error number 50001 defined previously:

[View full width]
declare @msg varchar(8000) select @msg = formatmessage(50001, 'Titles', 'Titleauthor') print @msg The row(s) from table Titles could not be deleted. There are rows in table Titleauthor graphics/ccc.gifthat refer to this row. Delete those rows first.

SET Options

You can use the SET command to alter a connection's behavior. Options set with the SET command stay active until the connection terminates. For most options, when they are set within a stored procedure, the option is set back to its connection level setting when the procedure returns.

Most SET commands take values of ON or OFF as arguments, whereas some take a specific value. Some of the SET statements do not take effect until the next batch, whereas others will be set at parsing or execution time.

The tuning-related SET parameters are generally used when analyzing and optimizing queries. They can give you information about how SQL Server executes a query and also, to some extent, control how a query is executed. The default option settings are noted by asterisks (*) in Table 26.20.

Table 26.20. Tuning-Related SET Parameters
Parameter Arguments Description
FORCEPLAN ON|OFF* SQL Server will process a JOIN in the same order as specified in the FROM clause.
NOEXEC ON|OFF* SQL Server will optimize the query but not execute it. NOEXEC was often used in conjunction with SHOWPLAN in releases of SQL Server prior to 7.0 and is not needed with SHOWPLAN_TEXT or SHOWPLAN_ALL. Note that no other commands will execute for a session until NOEXEC is set back off.
PARSEONLY ON|OFF* SQL Server will parse the query but not optimize or execute it. It is useful to check the syntax of a SQL batch before executing it. Note that no other commands will execute for a session until PARSEONLY is set back off.
QUERY_GOVERNOR_ COST_LIMIT value Overrides the server-level configuration setting for query governor cost limit. Permission to execute this setting is limited to members of the sysadmin role.
SHOWPLAN_ALL ON|OFF* Displays the query plan that SQL Server uses to execute the query, but it does not execute the query. This is intended for programs that parse the output, such as the Query Analyzer. For textual output, use SHOWPLAN_TEXT instead.
SHOWPLAN_TEXT ON|OFF* Displays the query plan that SQL Server uses to execute the query, but it does not execute the query.
STATISTICS_IO ON|OFF* Displays information regarding I/O activity for each query.
STATISTICS_TIME ON|OFF* Displays information regarding execution time for each query.
STATISTICS_PROFILE ON|OFF* Executes the query and displays the query plan that SQL Server uses to execute the query.

In Listing 26.22, you turn on SHOWPLAN_TEXT so that the execution plan is returned to the client.

Listing 26.22 Using SHOWPLAN_TEXT
SELECT title, au_fname, au_lname
 FROM titles t
   JOIN titleauthor ta ON t.title_id = ta.title_id
   JOIN authors a ON ta.au_id = a.au_id

SELECT title, au_fname, au_lname
 FROM titles t
   JOIN titleauthor ta ON t.title_id = ta.title_id
   JOIN authors a ON ta.au_id = a.au_id

(1 row(s) affected)

  |--Nested Loops(Inner Join, OUTER REFERENCES:([ta].[au_id]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[title_id]))
       |    |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))
       |    |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS
[ta]), SEEK:([ta].[title_id]=[t].[title_id]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]
AS [a]), SEEK:([a].[au_id]=[ta].[au_id]) ORDERED FORWARD)

For information on how to interpret the SHOWPLAN_TEXT or STATISTICS PROFILE information, see Chapter 36, "Query Analysis."

With the transaction-handling?related SET parameters, you can override SQL Server's default transaction-handling behavior. In Table 26.21, the default option settings are noted by asterisks (*).

Table 26.21. Transaction-Handling?Related SET Parameters
Parameter Argument Description
CURSOR_CLOSE_ON_COMMIT ON|OFF* Controls whether cursors should be automatically closed when a COMMIT TRAN statement is executed.
IMPLICIT_TRANSACTIONS ON|OFF* An implicit BEGIN TRANSACTION is triggered for most DML statements when IMPLICIT_ TRANSACTIONS is turned on. Transactions are only ended when an explicit COMMIT or ROLLBACK is issued.
LOCK_TIMOUT milliseconds Specifies the number of milliseconds a process will wait for a lock to be released before returning a locking error. The default value is ?1, which indicates no timeout period.
REMOTE_PROC_TRANSACTIONS ON|OFF* When enabled, a distributed transaction is started when a remote procedure call is executed within a local transaction.
TRANSACTION_ISOLATION_LEVEL READ_ COMMITTED* |READ_ UNCOMMITTED| REPEATABLE_ READ| SERIALIZABLE Specifies the degree of isolation between concurrent transactions.
XACT_ABORT ON|OFF* When this option is turned on, SQL Server will roll back the current transaction if a runtime error occurs.

In Listing 26.23, you turn on IMPLICIT_TRANSACTIONS, issue two DELETE statements, print the nesting level, and perform a ROLLBACK.

Listing 26.23 Setting IMPLICIT_TRANSACTIONS to Get an Implicit BEGIN TRANSACTION
DELETE FROM titleauthor WHERE title_id = 'BU1032'
DELETE FROM titles WHERE title_id = 'BU1032'
print 'Transaction nesting level is: ' + CAST(@@TRANCOUNT AS VARCHAR(5))
print 'Transaction nesting level is now: ' + CAST(@@TRANCOUNT AS VARCHAR(5))

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
 'FK__sales__title_id__0BC6C43E'. The conflict occurr

Part III: SQL Server Administration
Part IV: Transact-SQL
Part V: SQL Server Internals and Performance Tuning
Part VI: Additional SQL Server Features