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   statement_block  ELSE  statement_block  

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 BEGIN     PRINT "Dropping orders Table"     DROP TABLE orders END ELSE     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  statement_block 

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 */ while (select avg (price) from titles) <  begin     update titles set price = price * 1.05     /* if more than 10 books are less than     ** , continue processing */     if (select count(*) from titles            where price <) > 1         continue     /* If maximum price of any book exceeds     ** , exit loop */     if (select max(price) from titles) >          break end 

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

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

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

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 ' +        case           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 go 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 >  then price * .90                     else price * 1.15                end 


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 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]
[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.gif back', 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  go BEGIN TRAN UPDATE authors SET au_lname = au_lname WAITFOR DELAY '00:00:10' UPDATE titles SET title = title ROLLBACK TRAN 

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

 use pubs  go BEGIN TRAN UPDATE titles SET title = title UPDATE authors SET au_lname = au_lname ROLLBACK TRAN 

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 begin     EXEC('UPDATE STATISTICS ' + @tbl_name)     fetch c1 into @tbl_name end 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) GO EXEC general_select authors GO 

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
 CREATE TABLE orders    (order_number UNIQUEIDENTIFIER DEFAULT NEWID()  PRIMARY KEY NOT NULL,     stor_id char(4) REFERENCES stores(stor_id),     order_date DATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112)) GO CREATE VIEW recent_orders AS    SELECT order_number, stor_id, order_date       FROM orders       WHERE order_date > GETDATE()  14 GO 


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 go select * from publishers */ go 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 go 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:

 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 back', 12, 1) ROLLBACK TRANSACTION RETURN -101 

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_id INT IDENTITY PRIMARY KEY NOT NULL,    customer_name NVARCHAR(100) NOT NULL,    customer_comments NVARCHAR(1000) NULL) Go CREATE TABLE orders   (order_number UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY NOT NULL,    customer_number INT REFERENCES customers(customer_id),    order_date DATETIME DEFAULT CONVERT(CHAR(8), GETDATE(), 112)) GO 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) go 


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
 SELECT @@VERSION go 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' go Product Version -------------------- 8.00.534 Product Level ------------- SP2 

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 go Error Number ------------        50000 Hello world Error Number ------------            0 

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) GO -- If not, continue with whatever you want to do SELECT au_fname, au_lname FROM pubs..authors go 

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) go 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.gif in table %s that refer to this row. Delete those rows first.' go RAISERROR (50001, 16, 1, 'Titles', 'Titleauthor') go 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.gif that 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]
[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.gif that 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
 SET SHOWPLAN_TEXT ON GO 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 go StmtText ------------------------------------------------------------------------------- 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) StmtText -------------------------------------------------------------------------------   --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
 SET IMPLICIT_TRANSACTIONS ON GO DELETE FROM titleauthor WHERE title_id = 'BU1032' DELETE FROM titles WHERE title_id = 'BU1032' print 'Transaction nesting level is: ' + CAST(@@TRANCOUNT AS VARCHAR(5)) ROLLBACK TRAN print 'Transaction nesting level is now: ' + CAST(@@TRANCOUNT AS VARCHAR(5)) go Server: Msg 547, Level 16, State 1, Line 1 DELETE statement conflicted with COLUMN REFERENCE constraint  'FK__sales__title_id__0BC6C43E'. The conflict occurred in database 'pubs', table 'sales', column 'title_id'. The statement has been terminated. Transaction nesting level is: 1 Transaction nesting level is now: 0 

With the formatting-related SET parameters, you can specify how input and output values are formatted. For instance, you can specify the order in which day, month, and year parts are specified when entering data. In Table 26.22, asterisks note the default options (*). The default specified applies for the U.S. English language.

Table 26.22. SET Parameters That Control Data Formatting
Parameter Argument Description
DATEFIRST number Specifies which day is the last weekday. The default is 7 (Saturday).
DATEFORMAT mdy Specifies how SQL Server will interpret the date, month, and year parts when inserting datetime data. Valid formats include mdy , dmy , ymd , ydm , myd , and dym . The U.S. English default is dmy .
FMTONLY ON OFF * Only returns metadata to the client when turned on. The query is processed and result column information is returned, but no result rows are returned.
IDENTITY_INSERT tblname ON OFF * Allows you to insert an explicit value for an identity column when turned on.
LANGUAGE language_name Controls in which language error messages should be returned. The language must be available on the server. It also controls the language used when returning the weekday and month with the DATENAME function.
NOCOUNT ON OFF * Controls whether the number of rows affected by the last command should be returned to the client application. Even if turned off, the count is still available in the @@ROWCOUNT global variable.
OFFSETS ON OFF * Controls whether the offset for certain T-SQL keywords should be returned to DB-Library applications. Can only be used in DB-Library applications.
ROWCOUNT number Causes SQL Server to stop processing the query after the specified number of rows are processed. Note that this also applies to data modification statements. Use the TOP keyword if you want to con-trol how many rows to return from a specific SELECT statement.
TEXTSIZE number Controls how many bytes a SELECT statement returns from text and ntext columns. Note that ntext uses two bytes per character.

In Listing 26.24, you want to return the weekday and month that a book is published in the Swedish language ( assuming that the Swedish language is already installed).

Listing 26.24 Setting the Language to Display SQL Server Messages
 SET LANGUAGE Swedish SELECT '"' + RTRIM(title) + '" is published on a '        + DATENAME(dw, pubdate) + ' in ' + DATENAME(mm, pubdate) + '.'  FROM titles WHERE title_id = 'PC1035' go Changed language setting to Svenska. --------------------------------------------------------------- "But Is It User Friendly?" is published on a sndag in juni. 

Listing 26.25 sets the date format for specifying datetime data. Note that the SELECT statement uses three possible formats to specify datetime data.

Listing 26.25 Using SET DATEFORMAT to Specify Order of DateParts When Entering Datetime Data
 SET DATEFORMAT ymd GO SELECT CONVERT(smalldatetime, '1999.12.31') as 'Numeric',  CONVERT(smalldatetime, '19991231') as 'Unseparated',  CONVERT(smalldatetime, 'Dec 1999 31') as 'Alphabetic' go Numeric              Unseparated           Alphabetic -------------------- --------------------- --------------------- 1999-12-31 00:00:00  1999-12-31 00:00:00   1999-12-31 00:00:00 

The ANSI-related and miscellaneous SET parameters control behavior for comparison to NULL , division with 0, and so on. In Table 26.23, asterisks note the default options (*).

Table 26.23. ANSI and Miscellaneous SET Parameters
Parameter Argument Description
ARITHABORT ON OFF * Terminates a query if overflow or divide-by-zero occurs when turned on. Note that rows can be returned before the abort occurs.
ARITHIGNORE ON OFF * Returns NULL if overflow or divide-by-zero errors occur when turned on. No warning message is sent to the client. Default behavior is that NULL and a warning message are returned.
NUMERIC_ROUNDABORT ON OFF * Specifies whether an error message should be returned when a loss of precision occurs due to rounding the result value.
ANSI_NULL_DFLT_OFF ON OFF * Set this to ON if you do not want a column to allow NULL when you create a table and do not specify the NULL or NOT NULL column property. Use when the database option for ANSI NULL Default is set to true .
ANSI_NULL_DFLT_ON ON OFF * Set this to ON if you want a column to allow NULL when you create a table and do not specify the NULL or NOT NULL column property. Use when the database option for ANSI NULL Default is set to false .
ANSI_NULLS ON OFF * Controls how comparison to NULL should be handled. By default, it allows a NULL = NULL comparison to evaluate to TRUE . Setting this option to ON will follow the SQL-92 “compliant behavior that any comparison to NULL evaluates to UNKNOWN .


ON * OFF Determines whether a string concatenated with NULL returns NULL or the string. The default setting is ON , which returns NULL .
CONTEXT_INFO binary_data Provides the ability to programmatically associate up to 128 bytes of binary information with the current session or connection. This option allows you to set the context information stored in the context_info column in the master.dbo.sysprocesses table.
ANSI_PADDING ON OFF * Specifies whether char , varchar , binary , and varbinary columns should be padded with blanks or zeroes. By default, char and binary columns, whether defined to allow NULL s or not, will be padded out to the defined length with spaces or zeroes, respectively. For varchar and varbinary , values are not padded, but trailing spaces or zeroes inserted are not trimmed . The column behavior is determined by the ANSI_PADDING setting in effect at the time the table is created.
ANSI_WARNINGS ON OFF Generates a warning if an aggregate function is applied over rows that contain NULL and if INSERT or UPDATE specifies data with a length that exceeds the column definitions for character, Unicode, or binary data. A division by 0 or overflow results in the rollback of the statement if this option is set. This option is set to ON automatically when using ODBC or OLE DB.
ANSI_DEFAULTS ON OFF Controls a group of MS SQL Server settings that set behavior to SQL-92 standards. If set to ON , the following options will set on: ANSI_NULLS , ANSI_NULL_DFLT_ON , ANSI_PADDING , ANSI_WARNINGS , CURSOR_CLOSE_ON_COMMIT , IMPLICIT_TRANSACTIONS , and QUOTED_IDENTIFIER . This option is set to ON automatically when connecting via ODBC or OLE DB.
DEADLOCK_PRIORITY NORMAL * LOW If LOW , this connection will be the preferred victim if a deadlock occurs. If your application handles deadlock gracefully, set this to LOW to increase the chance that an application that does not handle deadlock can continue processing in the event of a deadlock situation.
FIPS_FLAGGER OFF * ENTRY INTERMEDIATE FULL Specifies whether SQL Server will generate a warning if a statement does not comply with the specified level of the FIPS 127-2 standard, which is based on the SQL-92 standard.
QUOTED_IDENTIFIER ON OFF * Specifies whether identifiers are delimited by double quotation marks. When set to OFF , identifiers cannot be quoted and strings can use double or single quotation marks. When set to ON , double quotation marks are used to delimit identifiers, and literals must use single quotation marks.

Listing 26.26 explores the differences when checking for the NULL symbol, depending on how ANSI_NULLS is set. The preferred way of checking for the NULL symbol is to use IS NULL and IS NOT NULL , which is consistent regardless of how ANSI_NULLS is set.

Listing 26.26 Checking for NULL
 SET ANSI_NULLS OFF GO --Note that both statements will return two rows. SELECT title_id, price FROM titles WHERE price = NULL SELECT title_id, price FROM titles WHERE price IS NULL go title_id price -------- ---------- MC3026   NULL PC9999   NULL title_id price -------- ---------- MC3026   NULL PC9999   NULL SET ANSI_NULLS ON GO --Note that the first statement will return zero rows. SELECT title_id, price FROM titles WHERE price = NULL SELECT title_id, price FROM titles WHERE price IS NULL go title_id price -------- ---------- title_id price -------- ---------- MC3026   NULL PC9999   NULL 
Checking Session Options

At times, you might find it necessary to identify which session-level options are in effect. For example, you might need to know if IMPLICIT_TRANSACTIONS is enabled so you know whether to issue a BEGIN TRAN statement in your code. You might need to know whether QUOTED_IDENTIFIER or CONCAT_NULL_YIELDS_NULL is enabled.

One way to find out what options are set is to use the @@OPTIONS function:

 select  @@OPTIONS  go -----------        5240 

The @@OPTIONS function returns an integer representation of the bitmap indicating which options are set for the current session. Table 26.24 lists the options and the bitmap value.

Table 26.24. @@OPTIONS Bitmap Values and the Corresponding Session Options
Value Session Option

The previous example returned a value of 5240 from @@OPTIONS . If you add up the appropriate bitmap values, you can determine which settings are enabled:

5240 = 4096 + 1024 + 32 + 64 + 16 + 8

Using the values in Table 22.24, this indicates that the following options are set:

  • arithabort

  • ansi_null_dflt_on

  • ansi_warnings

  • ansi_padding

  • ansi_nulls

  • concat_null_yields_null

Wasn't that fun? It's not the most intuitive way to determine what options are set. Fortunately, another command is available to use ” DBCC USEROPTIONS :

 dbcc useroptions  go Set Option                                                     Value -------------------------------------------------------------- ---------- textsize                                                       64512 language                                                       us_english dateformat                                                     mdy datefirst                                                      7 arithabort                                                     SET ansi_null_dflt_on                                              SET ansi_warnings                                                  SET ansi_padding                                                   SET ansi_nulls                                                     SET concat_null_yields_null                                        SET 

This information is helpful from a user perspective, but doesn't help you much in your T-SQL code because it is a DBCC command result and not a select statement; therefore, you can't really interrogate the results. Fortunately, you can use the SESSIONPROPERTY() function to determine whether specific settings are enabled. The SESSIONPROPERTY() function returns 1 if the specified option is set, or 0 if not. The following example checks to see if CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER are enabled:

 select case SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')           when 1 then 'Concat_null_yields_null is on'          else 'Concat_null_yields_null is off'          end,        case SESSIONPROPERTY('QUOTED_IDENTIFIER')           when 1 then 'Quoted Identifier is on'           else 'Quoted Identifier is off'           end go ------------------------------ ------------------------ Concat_null_yields_null is on  Quoted Identifier is off 

The following are the currently valid arguments for the SESSIONPROPERTY() function. Any other value passed to the function will return NULL :








To avoid having to set substantial options every time a user connects to SQL Server, a member of the sysadmin role can configure the default user options for all connections via the user options setting for sp_configure . To set the user options you want enabled, specify an integer representation of a bitmap using the same bitmap values shown previously in Table 28.24. For example, if you wanted all connections to have the CONCAT_NULL_YIELDS_NULL , QUOTED_IDENTIFIER , ANSI_NULLS settings enabled automatically, you could execute the following command:

 exec sp_configure 'user options', 4384 -- 4096 + 256 + 32  reconfigure 

To set no default session options, set user options to zero (0). You can also use sp_configure without the second parameter to view the current default user options:

 exec sp_configure 'user options'  go name                              minimum   maximum   config_value run_value --------------------------------- --------- --------- ------------ --------- user options                      0         32767     4384         4384 

No user-defined global variables exist, and local variables do not retain values between batches or stored procedures. The CONTEXT_INFO setting allows you to store information in the context_info column in the sysprocesses table in the master database. A row in sysprocesses exists for every connection to SQL Server, so the data will remain there until you disconnect from SQL Server.

The context_info column is a binary (128) column. You can store any data value into it with the SET CONTEXT_INFO command, but will have to deal with hexadecimal data when retrieving it. If you are handy at manipulating hexadecimal data, you can store multiple values in the context_info column. The following example stored the avg(price) from the titles table into the context_info column:

 declare @avg_price money  select @avg_price = avg(price) from titles set context_info @avg_price 

You can retrieve the value stored in context_info using a SELECT statement. You will need to convert the binary data back to money when you retrieve it. Because the average price is the only value stored in context_info , you can retrieve it by performing a substring on the first 8 bytes of the context_info column (the money datatype is 8 bytes in size). Because SQL Server assigns a unique server process ID (spid), you will use the @@SPID function to retrieve the information for the current connection:

 select convert(money, substring(context_info, 1, 8)) as AVG_PRICE  from master..sysprocesses where spid = @@spid go AVG_PRICE --------------------- 14.7662 

If you hadn't used substring to specify only the first 8 bytes of the context_info column, SQL Server would have assumed the money data was stored in the last 8 bytes and would have returned a result of 0:

 select convert(money, context_info) as AVG_PRICE  from master..sysprocesses where spid = @@spid go AVG_PRICE --------------------- .0000 

Because money can be implicitly converted to binary, you don't need to convert it when setting context_info . For some other datatypes, like char or datetime , you need to explicitly convert the data to binary because implicit conversions from those datatypes to binary is not supported. In the next example, you are appending a datetime value to the average price value already stored in context_info . You explicitly convert the datetime value to binary and append it to the 8 bytes you have already stored in context_info :

 declare @max_date datetime,          @context_info binary(128) select @max_date = max(pubdate) from titles select @context_info = substring(context_info, 1, 8)                         + convert(binary(8), @max_date)    from master..sysprocesses    where spid = @@spid set context_info @context_info 

You now have two values stored in context_info . Using the appropriate substring, you can retrieve either the average price or the maximum pubdate from context_info (note that the binary data converts implicitly to money and datetime ):

 declare @avg_price money,          @max_pubdate datetime select @avg_price = substring(context_info, 1, 8),        @max_pubdate = substring(context_info, 9, 8)    from master..sysprocesses    where spid = @@spid select @avg_price as 'Avg Price', @max_pubdate as 'Max PubDate' go Avg Price             Max PubDate --------------------- ------------------------------------------------------ 14.7662               2000-08-06 01:33:54.140 

Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: