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 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.
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.
/* loop until average price equals or exceeds $25*/ while (select avg (price) from titles) < $25 begin 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 continue /* If maximum price of any book exceeds ** $50, exit loop */ if (select max(price) from titles) > $50 break end
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.
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 > $15 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 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.
TIPYou 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 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.
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.
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.
/* 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 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.
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.
NOTEThe 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.
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.
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.
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
TIPBe 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.
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.
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
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!
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.
NOTEIf 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.
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.
-- 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.
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.
TIPTo 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. |
SQL Server error messages are stored in the sysmessages table in the master database. Table 26.19 describes the 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.
sp_addmessage 50001, 16, 'The row(s) from table %s could not be deleted. There are rows 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 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]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 that refer to this row. Delete those rows first.
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.
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.
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 (*).
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.
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 occurr