Advanced Query Techniques
Updated : July 19, 2001
Chapter 9 from Microsoft SQL Server 7.0 Database Implementation Training Kit, published by Microsoft Press
On This Page
About This Chapter
Lesson 1: Subqueries
Lesson 2: Using Cursors
Lesson 3: Modifying Data
Review
About This Chapter
This chapter adds to the query techniques described in Chapter 8, "Querying Multiple Indexes," and Appendix D, "Basic Queries." Subqueries make it possible to use queries inside of other queries to create calculated values and specify advanced selection criteria. Cursors are used to perform row-based processing on SQL result sets. The final lesson explains how to use the techniques, which you have learned to use with SELECT queries, when modifying data.
Before You Begin
To complete the lessons in this chapter you must:
Have installed Microsoft SQL Server 7.0.
Be able to log on SQL Server as an Administrator.
Know the basics of SQL. Appendix C covers basic SQL queries, including exercises.
Complete the exercises in Chapter 8 or run the c:\sqlimpl\exercise\bldlib\bldlib.cmd batch file with a chapter argument of 9.
Have installed the exercise files from the Supplemental Course Materials CD-ROM to your hard drive using the Setup.exe discussed in the "Getting Started" section of the book.
Lesson 1: Subqueries
A subquery is a SELECT statement nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. Subqueries can be nested or correlated; nested subqueries execute once when the outer query is executed, and correlated subqueries execute once for every row that is returned when the outer query is executed.
After this lesson, you will be able to:
- Use subqueries to break down and perform complex queries.
Estimated lesson time: 50 minutes
When to Use Subqueries
Use subqueries to break down a complex query into a series of logical steps and solve a problem with a single statement. Subqueries are useful when your query relies on the results of another query.
When to Use Joins Rather Than Subqueries
Often, a query that contains subqueries can be written as a join. Query performance may be similar with a join and a subquery. The difference is that a subquery may require the query optimizer to perform additional steps, such as sorting, which may influence the processing strategy.
Using joins typically allows the query optimizer to retrieve data in the most efficient way. If a query does not require multiple steps, it may not be necessary to use a subquery.
Using Subqueries
When you decide to use subqueries, consider the following facts and guidelines:
You must enclose subqueries in parentheses.
You can only use one expression or column name in the select list of a subquery that returns a value.
You can use subqueries in place of an expression as long as a single value or list of values is returned.
You cannot use subqueries on columns that contain text and image data types.
You can have as many levels of subqueries as you need—there is no limit.
The query that contains the subquery is usually called the outer query and a subquery is called an inner query.
Nested Subqueries
Nested subqueries return either a single value or a list of values. SQL Server evaluates a nested subquery once and then uses the value or list returned by the subquery in the outer query.
Returning a Single Value
Use a nested subquery that returns a single value to compare to a value in the outer query. When you use nested subqueries to return a single value, consider the following guidelines:
Single-value subqueries can be used anywhere that an expression can be used.
In a WHERE clause, use the nested subquery with a comparison operator.
If a nested subquery returns more than one value when the outer query expects only one value, SQL Server displays an error message.
You can use an aggregate function or specify an expression or column name to return a single value.
Each nested subquery is evaluated only once.
Example 1
This example uses a single-value nested SELECT statement in the WHERE clause to return all customers in the Orders table who placed orders on the most recent recorded day.
USE Northwind SELECT OrderID, CustomerID FROM Orders WHERE OrderDate = (SELECT MAX(OrderDate) FROM Orders)
Result
OrderID CustomerID ————— ————— 11074 SIMOB 11075 RICSU 11076 BONAP 11077 RATTC (4 row(s) affected)
To fully understand this example, consider the result if the subquery is executed on its own:
SELECT MAX(OrderDate) FROM Orders
Result
————————————— 1998-05-06 00:00:00.000 (1 row(s) affected)
Therefore, the outer query is effectively:
USE Northwind SELECT OrderID, CustomerID FROM Orders WHERE OrderDate = '1998-05-06 00:00:00.000'
Example 2
This example contains two single-value subqueries. For each sale of a particular product, the query lists the quantity sold, the total sales of all products and the percentage of total sales for that particular sale.
USE Northwind
SELECT
ProductID,
Quantity AS Number_Sold,
(SELECT SUM(Quantity) FROM [Order Details]) AS Total_Sold,
((CONVERT(Money, Quantity) /
(SELECT SUM(Quantity) FROM [Order Details])) * 100)
AS Percent_Of_Sales
FROM [Order Details]
Result
ProductID Number_Sold Total_Sold Percent_Of_Sales ————— ————— ————— —————————— 11 12 51317 .0200 42 10 51317 .0100 72 5 51317 .0000 … 75 4 51317 .0000 77 2 51317 .0000 (2155 row(s) affected)
To use a single-value subquery
In this exercise, you will write and execute a query in the library database that returns member.firstname, member.lastname, loanhist.isbn, and loanhist.fine_paid for members who have paid the highest recorded fines for all books. The c:\sqlimpl\exercise\ch09\highpay.sql script file contains a completed script for this exercise.
Open SQL Server Query Analyzer.
Write a query that returns the largest recorded value in the loanhist.fine_paid column.
Write a query that joins the member and loanhist tables and returns the firstname, lastname, isbn, and fine_paid for each row.
Use the first query as selection criteria in the WHERE clause of the second query to return only those rows from the join in which the fine that is paid equaled the largest value that was ever recorded for all books.
Include the DISTINCT keyword in your query to eliminate entries for members who have paid this fine on several occasions.
USE library SELECT DISTINCT firstname, lastname, isbn, fine_paid FROM member m JOIN loanhist lh ON m.member_no = lh.member_no WHERE lh.fine_paid = (SELECT MAX(fine_paid) FROM loanhist)
Result
firstname lastname isbn fine_paid ——————— ——————— ————— —————————— Angela Valentine 103 8.0000 Shelly LaBrie 293 8.0000 Teresa Chen 291 8.0000 (3 row(s) affected) Warning: Null value eliminated from aggregate.
Returning a List of Values
To test for membership in a generated list of values, use a nested subquery that returns a list of values. When you use nested subqueries to return a list of values, consider the following facts:
In a WHERE clause, use the IN operator with subqueries that return a list of values.
Each nested subquery is evaluated only once.
Example 1
This example generates a list of companies that made purchases after the date 1/1/95.
USE Northwind SELECT CompanyName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate > '1/1/95')
Result
CompanyName ———————————————————— Alfreds Futterkiste Ana Trujillo Emparedados y helados Antonio Moreno Taquería … Wilman Kala Wolski Zajazd (89 row(s) affected)
To fully understand this example, consider the result if the subquery is executed on its own:
SELECT CustomerID FROM Orders WHERE OrderDate > '1/1/95'
Result
CustomerID ————— VINET TOMSP HANAR … BONAP RATTC (830 row(s) affected)
Therefore, the outer query is effectively:
USE Northwind
SELECT CompanyName
FROM Customers
WHERE CustomerID IN
('VINET', 'TOMSP', 'HANAR', … , 'BONAP', 'RATTC')
To use a multiple-value subquery
In this exercise, you will write and execute a query in the library database on the reservation table that returns two columns, member_no, and DateReserved. The query returns a list of member numbers that have books on reserve with a title that contains the word First. The c:\sqlimpl\exercise\ch09\subquin.sql script file contains a completed script for this exercise.
Write a subquery that joins the item and title tables and returns a list of ISBN numbers for all items that have titles that contain the word First.
Write an outer query that returns two columns: member_no and DateReserved, where DateReserved is the log_date converted using the CONVERT function and date style 1.
Restrict the rows that form the groups in the outer query by comparing reservation.isbn against the list of values that were generated by the subquery that you wrote in step 1. Use the IN keyword as part of the WHERE clause.
USE library SELECT member_no, CONVERT(char(8), log_date, 1) AS DateReserved FROM reservation WHERE isbn IN (SELECT it.isbn FROM item it INNER JOIN title ti ON it.title_no = ti.title_no WHERE ti.title LIKE '%First%')
Result
The DateReserved column will be a different date on your system.
member_no DateReserved ———— —————— 35 01/12/99 86 01/12/99 137 01/12/99 … 9929 01/12/99 9980 01/12/99 (196 row(s) affected)
Correlated Subqueries
For correlated subqueries, the inner query uses information from the outer query and executes once for every row in the outer query.
When you use correlated subqueries, consider the following facts and guidelines:
You must use aliases to distinguish table names.
Correlated subqueries can usually be rephrased as joins. Using joins rather than correlated subqueries enables SQL Server query optimizer to determine how to correlate the data in the most efficient way.
Example 1
This example returns a list of customers who ordered more than 20 items of product number 23 on a single order.
USE Northwind
SELECT OrderID, CustomerID
FROM Orders o
WHERE 20 < (SELECT Quantity
FROM [Order Details] od
WHERE o.OrderID = od.OrderID AND od.ProductID = 23)
Result
OrderID CustomerID ————— ————— 10337 FRANK 10348 WANDK 10396 FRANK … 10869 SEVES 10880 FOLKO (11 row(s) affected)
Figure 9.1 and the following steps describe how the correlated subquery is evaluated in Example 1:
The outer query passes a column value to the inner query. The outer query passes the value from the OrderID column to the inner query.
The inner query uses the value that the outer query passes. The OrderID from the Orders table is used in the search condition of the WHERE clause of the inner query. The inner query tries to find a row in the [Order Details] table that matches the OrderID from the outer query and the ProductID 23.
The inner query returns a value back to the outer query. If a row was found in the inner query, the value from the Quantity column of that row is returned to the outer query; otherwise, NULL is returned to the outer query. The WHERE clause of the outer query then evaluates its search condition to determine whether the Quantity exceeds 20, in which case the order is included in the result set of the outer query.
Move on to the next row in the Orders table. The outer query moves on to the next row and SQL Server repeats the evaluation process for that row.
.gif "Cc917631.f09xx01(en-us,TechNet.10).gif")
Figure 9.1: Evaluating a correlated subquery.
Example 2
This example returns a list of products and the largest order ever placed for each product. Notice that this correlated subquery uses a second alias to reference the same table as the outer query. The Products table joined simply to look up the product name; it is not involved in the subquery.
USE Northwind SELECT DISTINCT ProductName, Quantity FROM [Order Details] od1 JOIN Products p ON od1.ProductID = p.ProductID WHERE Quantity = (SELECT MAX(Quantity) FROM [Order Details] od2 WHERE od1.ProductID = od2.ProductID)
Result
ProductName Quantity ———————————————————— ———— Alice Mutton 100 Aniseed Syrup 60 Boston Crab Meat 91 … Wimmers gute Semmelknödel 130 Zaanse koeken 55 (77 row(s) affected)
To use a correlated subquery
In this exercise, you will create a query that uses a correlated subquery to calculate a value based on data from the outer query, and then uses that value as part of a comparison. You will query the member and loanhist tables to return a list of library members who have assessed fines that total more than $5.00. A correlated subquery calculates the fines that are assessed for each member. The c:\sqlimpl\exercise\ch09\fineof5.sql script file contains a completed script for this exercise.
Write a query that returns the member_no and lastname columns of the member table. Use a table alias for the member table.
Write a subquery that calculates the total fines that are assessed for each member as recorded in the loanhist table. Use an alias for the loanhist table. Correlate the member.member_no column of the outer query to the loanhist.member_no column of the inner query.
Use a comparison operator in the WHERE clause of the outer query to select those members who have fines that total more than $5.00.
USE library SELECT member_no, lastname FROM member m WHERE (SELECT SUM(fine_assessed) FROM loanhist lh WHERE m.member_no = lh.member_no) > 5
Result
member_no lastname ———— ——————— 203 Graff 617 Valentine 778 Chen … 9676 Harui 9806 Martin (41 row(s) affected)
Using the EXISTS and NOT EXISTS Keywords
You can use the EXISTS and NOT EXISTS keywords to determine whether a subquery returns rows.
Use with Correlated Subqueries
Use the EXISTS and NOT EXISTS keywords with correlated subqueries to restrict the result set of an outer query to rows that satisfy the subquery. The EXISTS and NOT EXISTS keywords return TRUE or FALSE, based on whether rows are returned by the subquery. The subquery is tested to see if it will return rows; it does not return any data. Use * (asterisk) in the select list of the subquery as there is no reason to use column names.
Example 1
This example uses a correlated subquery with an EXISTS keyword in the WHERE clause to return a list of employees who received orders on 9/2/94.
USE Northwind SELECT LastName, EmployeeID FROM Employees e WHERE EXISTS (SELECT * FROM Orders WHERE e.EmployeeID = Orders.EmployeeID AND OrderDate = '9/5/97')
Result
LastName EmployeeID —————————— ————— Peacock 4 King 7 (2 row(s) affected)
Example 2
This example returns the same result set as Example 1 to show that you could use a join operation rather than a correlated subquery.
USE Northwind SELECT LastName, e.EmployeeID FROM Orders INNER JOIN Employees e ON Orders.EmployeeID = e.EmployeeID WHERE OrderDate = '9/5/97'
Result
LastName EmployeeID —————————— ————— Peacock 4 King 7 (2 row(s) affected)
Lesson Summary
Subqueries allow you to use queries within other queries anywhere that you can use an expression or in WHERE clause search conditions. Nested subqueries are executed once, when the outer query executes and can return either a single value or a list of values for use by the outer query. Correlated subqueries are passed a value and then executed for each row in the outer query. The EXISTS and NOT EXISTS keywords are used in WHERE clauses to test for the existence of rows in the result set of a subquery.
Lesson 2: Using Cursors
Cursors offer the ability to navigate forward and backward through a result set one row at a time in order to view or process data. Think of a cursor as a pointer to a current position or a specific row within a result set.
After this lesson you will be able to:
Describe when to use Transact-SQL cursors.
Determine which cursor type and characteristics are appropriate for a cursor.
Create and use a cursor to retrieve data.
Estimated lesson time: 60 minutes
Cursors extend standard result set processing by:
Allowing positioning at a specific row in the result set.
Retrieving and modifying one row or block of rows from the current position in the result set.
Supporting different levels of sensitivity to changes to the data underlying the result set.
Requesting Cursors
Microsoft SQL Server version 7.0 supports two methods for requesting a cursor:
Transact-SQL
Application programming interface (API) cursor functions
This lesson discusses only Transact-SQL cursors in detail.
SQL Server supports the cursor functionality of the following database APIs and object interfaces:
ADO (ActiveX Data Object)
OLE DB
ODBC (Open Database Connectivity)
DB-Library
All cursors require temporary resources to cache data. These resources can be in the form of RAM, a paging file (such as the virtual memory feature of Microsoft Windows), temporary files, or databases. You should not create large cursors or use cursors unnecessarily because excessive use of these temporary resources may degrade the performance of SQL Server and user applications.
Cursors are cached on the client or the server under the following circumstances:
When you request a client cursor, SQL Server sends the entire result set across the network to the client.
When you request a server cursor, SQL Server uses its own resources to manage temporary objects. Only rows that are selected within the cursor are returned to the client over the network.
API cursors may be cached on the client or the server. Transact-SQL cursors are always cached on the server.
Transact-SQL Cursors
This lesson discusses the uses of Transact-SQL cursors, how Transact-SQL cursors work, the types and characteristics of Transact-SQL cursors, and the syntax for using Transact-SQL cursors. It also describes how to work with data from cursors and how to gather information about cursors.
Uses of Transact-SQL Cursors
Use Transact-SQL cursors for complex, row-oriented functionality.
Building a Command String for Execution
You can use Transact-SQL cursors to build command strings that include parameters such as database object names or data from user tables. You might use a cursor in a stored procedure to execute a Transact-SQL statement (such as UPDATE STATISTICS) on many related objects in a database.
Increasing Script Readability
Using a cursor rather than a nested SELECT statement can sometimes improve the readability of a stored procedure. If this is the case and if the performance of the two queries is comparable, using a Transact-SQL cursor is appropriate.
Performing Multiple Unrelated Manipulations with Data
You can pass local cursors between stored procedures when you want to generate a result set once and perform unrelated manipulations on the result set. For example, you can use a cursor to offer users multiple ways to view the data in a result set.
Compensating for Database and Application Limitations
You also can use cursors to compensate for database and application limitations. An example of this is the sp_helptext system stored procedure. This system stored procedure uses a Transact-SQL cursor to break down the text of a database object into multiple rows because previous versions of SQL Server had a 255-character limit on the char data type.
How Transact-SQL Cursors Work
Follow the steps illustrated in Figure 9.2 when you work with Transact-SQL cursors.
Declare the cursor. This includes the SELECT statement that generates a result set and defines the characteristics of the cursor, such as whether the rows in the cursor can be updated.
Execute the OPEN statement to generate the result set and populate the cursor.
Retrieve rows from the cursor result set. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches is called scrolling.
Use values from or perform operations on the row at the current position of the cursor.
Close and deallocate the cursor.
.gif "Cc917631.f09xx02(en-us,TechNet.10).gif")
Figure 9.2: How Transact-SQL cursors work.
Example
This example, used in Figure 9.2, creates a cursor based on a query that returns the names of the tables in the Northwind database from the sysobjects system table. After the cursor is opened, the script navigates through the cursor result set, executing the sp_spaceused system stored procedure for each table. When the cursor processing is complete, the cursor is closed and deallocated.
USE Northwind
DECLARE tbl_space CURSOR
FOR
SELECT name FROM sysobjects
WHERE type = 'U' ORDER BY name
OPEN tbl_space
DECLARE @tbl_name sysname
FETCH NEXT FROM tbl_space INTO @tbl_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_spaceused @tbl_name
FETCH NEXT FROM tbl_space INTO @tbl_name
END
CLOSE tbl_space
DEALLOCATE tbl_space
Results
name rows reserved data … —————————— ————— ————————— ——— Categories 8 184 KB 112 KB name rows reserved data … —————————— ————— ————————— ——— CustomerCustomerDemo 0 32 KB 8 KB … name rows reserved data … —————————— ————— ————————— ——— Territories 53 32 KB 8 KB
Types of Transact-SQL Cursors
SQL Server supports four types of cursors: forward-only, static, dynamic, and keyset-driven. The different types of cursors vary in their ability to detect changes to the result set and in the resources, such as memory and space in the tempdb database, that they use.
If you have a cursor open and the data in the underlying table, on which the cursor is based, is changed by other connections, different cursor types may or may not reflect those changes. The following table shows how the cursor type that you choose determines whether your cursor result set reflects changes that are made to the membership, order, or values of the data in the underlying table:
Cursor type |
Membership |
Order |
Values |
|---|---|---|---|
Forward-only |
Dynamic |
Dynamic |
Dynamic |
Static |
Fixed |
Fixed |
Fixed |
Dynamic |
Dynamic |
Dynamic |
Dynamic |
Keyset-driven |
Fixed |
Fixed |
Dynamic |
Forward-only
A forward-only cursor only supports fetching rows serially from the first to the last row of the cursor. It does not retrieve rows from the database until the rows are fetched from the cursor. The effects of all INSERT, UPDATE, and DELETE statements that any other connection makes before a row is fetched are visible when the row is fetched.
Static
A static cursor fixes a result set when the cursor is opened and the result set is always read-only. Thus, it is not possible to update the underlying tables of a static cursor through the cursor. The result set is stored in tempdb when the static cursor is opened. Changes made by other connections after the cursor is opened are never reflected by the cursor. Static cursors are also called insensitive or snapshot cursors.
Dynamic
Dynamic cursors are the opposite of static cursors. A dynamic cursor reflects all changes that are made to the data values, order, and membership of rows in the result set when you scroll through a cursor result set. The effects of all UPDATE, INSERT, and DELETE statements that any user makes are visible as rows are fetched through the cursor. You can fetch rows randomly from anywhere in the cursor. Dynamic cursors are also called sensitive cursors.
Keyset-driven
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a unique set of identifiers (keys), known as a keyset. The keys are built from a set of columns that uniquely identify a set of rows in the result set. The keyset is the set of key values from all the rows that qualified for the SELECT statement when the cursor was opened, and the membership and order of rows is never updated.
Inserts and updates that the user makes through the cursor are visible, as are changes that other connections make to data values in nonkeyset columns. The keyset for a keyset-driven cursor is stored in tempdb when it is the cursor opened.
Note: Cursors with fixed characteristics of membership, order, and values perform faster than cursors with dynamic characteristics, although they may be slower to open initially.
Consider the following facts when you determine which type of cursor to use:
Dynamic cursors open faster than static or keyset-driven cursors.
In joins, keyset-driven and static cursors can be faster than dynamic cursors.
Static and keyset-driven cursors increase the use of the tempdb database. Static server cursors build the entire cursor in tempdb; keyset-driven cursors build the keyset in tempdb. For this reason, if a large number of users each open a static or keyset-driven cursor, tempdb may run out of space.
Transact-SQL Cursor Characteristics
You can define the behavior and locking characteristics of cursors. Transact-SQL cursors and ODBC API cursors support specifying scrollability and sensitivity. Not all API cursors support specifying behaviors. All cursors support varying levels of locking.
Scrollability
Scrollability defines the fetch options that the cursor supports. Cursors can be scrollable or forward-only. A scrollable cursor supports all fetch options; a forward-only cursor supports only the fetch next option.
Sensitivity
Sensitivity defines whether updates that are made against the base rows are visible through the cursor. Sensitivity also defines whether you can make updates through the cursor. If you make an update to the current row of a cursor, the actual update is made to the underlying table; this is called a positioned update.
A sensitive cursor reflects data modifications that anyone makes. Positioned updates can be made through the cursor, except when a read-only sensitive cursor is used. An insensitive cursor is read-only and does not support updates.
Locking
Because SQL Server must acquire an exclusive lock before it can update a row, updates that are made through a cursor can be blocked by other connections that hold a shared lock on a row. The transaction isolation level of a cursor allows a programmer to determine the full locking behavior of a specific cursor environment.
When any row in a cursor is updated, SQL Server locks it with an exclusive lock. Locks are held for varying lengths of time, depending on the situation:
If the update is performed within a transaction, the exclusive lock is held until the transaction is terminated.
If the update is performed outside of a transaction, the update is committed automatically and when it is complete, the exclusive lock is freed.
Transact-SQL Cursor Syntax
You use five statements when you work with Transact-SQL cursors. The following table summarizes these five statements:
Statement |
Description |
|---|---|
DECLARE CURSOR |
Defines cursor structure and allocates resources |
OPEN |
Populates a declared cursor with a result set |
FETCH |
Navigates within a cursor result set |
CLOSE |
Releases the current result set and frees any cursor locks held on the rows on which the cursor is positioned |
DEALLOCATE |
Removes the cursor definition and deallocates resources |
DECLARE CURSOR
The DECLARE CURSOR statement defines the characteristics of the cursor, the query that the cursor uses, and any variables that the cursor uses.
There are two forms of the DECLARE CURSOR syntax. The first form of the DECLARE CURSOR statement uses the ANSI SQL-92 syntax. The second form uses the extended Transact-SQL cursor syntax.
ANSI SQL-92 Syntax
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,…n]]}]
Transact-SQL Syntax
DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,…n]]]
Consider the following facts when you declare cursors:
The select_statement is a standard SELECT statement. Certain keywords, such as DISTINCT and UNION, force the cursor type to static.
LOCAL or GLOBAL specifies the scope of the cursor. The cursor can be local to a stored procedure, trigger, or Transact-SQL script in which case it can be used only by that code and is removed when the code is finished executing. If the cursor is global, then it may be used by subsequent stored procedures, triggers or Transact-SQL scripts, executed on the connection, and is not removed until the connection is closed or until the cursor is closed and deallocated. The default is global.
The current user must have SELECT permission on the table and the columns that are used in the cursor.
OPEN
The OPEN statement opens and populates the cursor by executing the SELECT statement. If the cursor is declared with the INSENSITIVE or STATIC options, OPEN creates a temporary table to hold the rows of the cursor. If the cursor is declared with the KEYSET option, the OPEN statement creates a temporary table to hold the keyset.
Syntax
OPEN{ {[GLOBAL] cursor_name} | @cursor_variable_name}
Note: The @cursor_variable_name option allows you to reference a cursor that has been associated with a cursor variable. This is generally used in stored procedures.
FETCH
The FETCH statement retrieves a specific row from the cursor.
Syntax
FETCH
[ [NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {n | @nvar}
| RELATIVE {n | @nvar}]
FROM ]
{{[GLOBAL] cursor_name} | @cursor_variable_name}
[INTO @variable_name[,...n]]
Consider the following facts when you use the FETCH statement:
The type of FETCH option that is supported within a cursor depends on the type of declared cursor.
The @@FETCH_STATUS function is updated at every execution of the FETCH statement. Use @@FETCH_STATUS before you attempt to operate on the data to determine whether a fetch was a success or a failure.
CLOSE
The CLOSE statement releases the current result set. It releases any locks that are held on the row on which the cursor is positioned but leaves the data structures accessible for reopening. Modifications and fetches are not allowed until the cursor is reopened. Closed cursors may be reopened.
Syntax
CLOSE {{[GLOBAL] cursor_name} | @cursor_variable_name}
DEALLOCATE
The DEALLOCATE statement removes the association between a cursor and a cursor name or cursor variable that references the cursor. If the cursor name or cursor variable is the last reference to the cursor, the data structures used by the cursor are released.
Syntax
DEALLOCATE {{[GLOBAL] cursor_name} | @cursor_variable_name}
Working with Data from Cursors
In addition to navigating through data in cursors, you can modify data through a cursor or share the data.
Modifying Data through Cursors
You can modify data through local and global Transact-SQL cursors. You must declare the cursor as updateable and you must not declare it as read-only. In an updateable cursor, you can use UPDATE or DELETE statements with the WHERE CURRENT OF cursor_name clause to modify the current row. In the following example a cursor called ShipCurs has been created on the Shippers table in the Northwind database and the CompanyName column of the shipper in the current row is updated:
USE Northwind UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.' WHERE CURRENT OF ShipCurs
Passing Local Cursors between Stored Procedures
Stored procedures that declare and open local cursors can pass the cursors out for use by the calling stored procedure, trigger, or batch. Passing a cursor is done by declaring a variable with the cursor varying data type as an output parameter. Variables that are used to pass cursors are called cursor variables.
Consider the following facts about cursor variables:
Cursor variables can only be used as output parameters; they cannot be used as input parameters.
Local variables can also be declared with the cursor data type to hold a reference to a local cursor.
Pass local cursors between stored procedures when you want to generate a result set once and perform unrelated manipulations on the result set. For example, you could pass a local cursor to allow users to choose from a list of manipulations to perform on the cursor result set.
Getting Cursor Information
Various system stored procedures and functions provide information about
cursors.
System Stored Procedures
When a cursor has been declared, you can use the following system stored procedures to determine the characteristics of the cursor. These system stored procedures do not return a standard result set. They all report their output as an output cursor, so you have to declare a cursor variable and then fetch the rows from the cursor in order to use them.
System stored procedure |
Description |
|---|---|
sp_cursor_list |
Returns a list of cursors that are currently opened by the connection and the attributes of the cursors |
sp_describe_cursor |
Describes the attributes of a cursor, such as whether it is forward-only or scrolling |
sp_describe_cursor_columns |
Describes the attributes of the columns in the cursor result set |
sp_describe_cursor_tables |
Describes the base tables that the cursor accesses |
Functions
Many functions return information about the status of a cursor. The following table describes three of the more commonly used functions:
Global variable |
Description |
|---|---|
@@FETCH_STATUS |
Returns the status of the last cursor FETCH statement that was issued against any cursor that is currently opened on the connection |
@@CURSOR_ROWS |
Returns the number of qualifying rows in the last cursor that is opened on the connection |
CURSOR_STATUS |
After calling a stored procedure that returns a cursor as an output parameter, the function allows you to determine the status of the cursor that was returned. |
To execute a script that uses a cursor to find the median unitprice in the Products table
In this exercise, you will execute a script that creates a cursor to find the median unit price of all products in the Northwind database. If the cursor holds an odd number of products, the median is the middle row. If the cursor holds an even number of products, the median is the average of the two middle rows. A scrollable cursor is used so that the middle row(s) can be retrieved directly using an absolute fetch.
Open SQL Server Query Analyzer.
Open the c:\sqlimpl\exercise\ch09\cursor.sql script file. Review and then execute the script.
Lesson Summary
Cursors make it possible to perform row-based operations on SQL result sets. Transact-SQL provides syntax for creating cursors on SQL Server; you can also use API-based client and server cursors with SQL Server 7.0. Different types of cursors are available. The different types vary in their scrollability, sensitivity, and locking characteristics.
When you declare and open a Transact-SQL cursor, a temporary structure is created in the tempdb database to cache some or all the cursor data. The FETCH statement is then used to position the row pointer in the cursor and retrieve individual rows.
Lesson 3: Modifying Data
You can modify data with subqueries by using the INSERT…SELECT statement and delete or update data in a table based on data from another table by using the DELETE or UPDATE statements.
After this lesson you will be able to:
Insert data into an existing table from another table using the INSERT…SELECT statement.
Use subqueries and the FROM clause to use other tables when specifying criteria for the DELETE and UPDATE statements.
Estimated lesson time: 60 minutes
Using the INSERT…SELECT Statement
In Chapter 8, "Querying Multiple Indexes," you learned about the SELECT…INTO statement, which allowed you to create a new table from the result set of a SELECT statement. The INSERT…SELECT statement adds rows to an existing table by inserting the result set of a SELECT statement into the table.
Using the INSERT…SELECT statement is more efficient than writing multiple, single-row INSERT statements. When you use the INSERT…SELECT statement, consider the following facts and guidelines:
All rows that satisfy the SELECT statement are inserted into the outermost table of the query.
The table that receives the new rows must already exist in the database.
The columns of the table that receives the new rows must have data types that are compatible with the columns of the table source.
Determine whether a default value exists or whether a null value is allowed for any columns that are omitted from the SELECT statement. If there is no default and null values are not allowed, you must provide values for these columns.
Syntax
INSERT [INTO] table_name SELECT select_list FROM table_list WHERE search_conditions
Example
This example adds new customers to the Customers table. Employees of Northwind Traders are eligible to buy company products. This query contains an INSERT statement with a SELECT clause that adds employee information to the Customers table.
The value for the CustomerID column will be generated from the first three letters of the employee's first name, concatenated with the first two letters of the last name. The employee's last name is used in the CompanyName column, and the first name is used in the ContactName column.
USE Northwind INSERT Customers SELECT UPPER(SUBSTRING(FirstName, 1, 3) + SUBSTRING(LastName, 1, 2)), LastName, FirstName, Title, Address, City, Region, PostalCode, Country, HomePhone, NULL FROM Employees
To insert values from one table into an existing table
In this exercise, you will insert rows into the adult table of the library database for all juvenile members over age 18 by using the SELECT clause of the INSERT statement. You will delete the rows from the juvenile table in another exercise. The c:\sqlimpl\exercise\ch09\juv2adlt.sql script file contains a completed script for this exercise.
Write a SELECT statement that returns the member_no column from the juvenile table and the street, city, state, zip, and phone_no columns from the adult table. Also include in the select list today's date plus one year by using the following expression:
(DATEADD(YY, 1, GETDATE())
This last column will be used later, to provide a value for the adult.expr\_date column. This SELECT statement joins the juvenile table with the adult table; the join condition is juvenile.adult\_member\_no = adult.member\_no.
Write an INSERT statement that incorporates the SELECT statement that you created in step 1 in order to add rows to the adult table. Include a WHERE clause to limit the rows that are added to those members in the juvenile table who are over age 18 by using the following expression:
(DATEADD(YY, 18, ju.birth_date) < GETDATE()) USE library INSERT adult SELECT ju.member_no, ad.street, ad.city, ad.state, ad.zip, ad.phone_no, DATEADD(YY, 1, GETDATE()) FROM juvenile ju JOIN adult ad ON ju.adult_member_no = ad.member_no WHERE (DATEADD(YY, 18, ju.birth_date) < GETDATE())
Execute the query.
Write a query to verify that juvenile members were added to the adult table. The c:\sqlimpl\exercise\ch09\juv_test.sql script file is an example of this query.
Deleting Rows Based on Other Tables
Use the DELETE statement with joins or subqueries to remove rows from a table based on data stored in other tables. This is more efficient than writing multiple, single-row DELETE statements.
Using an Additional FROM Clause
In a DELETE statement, the WHERE clause references values in the table itself and is used to decide which rows to delete. If you use a FROM clause, you can reference other tables to make this decision. When you use the DELETE statement with a FROM clause, consider the following facts:
You can use the optional FROM keyword before the table name from which the rows are deleted. Do not confuse this keyword with the FROM clause.
The FROM clause may introduce a join and acts as the restricting criteria for the DELETE statement.
Partial Syntax
DELETE [FROM] {table_name | view_name}
[FROM {table_or_view | joined_table}[,…n]]
[WHERE {search_condition | CURRENT OF cursor_name}]
Example 1
This example uses a join operation with the DELETE statement to remove customers who reside in the United States (USA) from the Orders table of the Northwind database. (You cannot actually execute this example, as it will violate referential integrity constraints.)
USE Northwind DELETE FROM Orders FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE Country = 'USA'
Specifying Conditions in the WHERE Clause
You also can use subqueries to determine which rows to delete from a table based on rows of another table. You can specify the conditions in the WHERE clause rather than using an additional FROM clause. Use a nested or correlated subquery in the WHERE clause to determine which rows to delete.
Example 2
This example removes the same customers as Example 1 and shows that you can convert a join operation to a nested subquery. (You cannot actually execute this example, as it will violate referential integrity constraints.)
USE Northwind DELETE FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'USA')
To delete rows from a table based on criteria in other tables
In this exercise, you will create a query that deletes rows from the juvenile table that have matching rows in the adult table of the library database. After juvenile members are converted to adult members, those members must be deleted from the juvenile table. The c:\sqlimpl\exercise\ch09\del_juv1.sql script file contains a completed script for this exercise.
Write a SELECT statement that joins the adult and juvenile tables so that juvenile.member_no = adult.member_no.
Write a DELETE statement that uses the SELECT statement that you created in step 1 with an additional FROM clause to delete these rows from the juvenile table.
USE library DELETE FROM juvenile FROM juvenile INNER JOIN adult ON juvenile.member_no = adult.member_no
Execute the query.
Write a SELECT statement to verify that the juveniles that were added to the adult table have been removed from the juvenile table. The c:\sqlimpl\exercise\ch09\del_test.sql script file is an example of this query.
Updating Rows Based on Other Tables
Use the UPDATE statement with a FROM clause or a subquery to modify a table based on values from other tables.
Using the UPDATE Statement
When you use joins and subqueries with the UPDATE statement, consider the following facts and guidelines:
SQL Server never updates the same row twice in a single UPDATE statement. This built-in restriction minimizes the amount of logging that occurs during updates.
Use the SET keyword to introduce the list of columns or variable names to be updated. Any column prefix that is specified in the SET clause must match the table or view name that is specified after the UPDATE keyword.
Tables introduced by subqueries or the FROM clause cannot be updated in the UPDATE statement.
Partial Syntax
UPDATE {table_name | view_name}
SET column_name={expression | DEFAULT | NULL} [,…n]
{[FROM {table_or_view | joined_table}[,…n]]
[WHERE search_conditions ]}
| WHERE CURRENT OF cursor_name
Using Joins
Use the FROM clause to specify tables and joins that are used to provide the criteria for the UPDATE statement.
Example 1
This example uses a join to update the number of sales-to-date of each product in the Products table. Assume that a ToDateSales column has been added to the Products table. This query does not generate the expected results. Each row in the Products table is only updated once, so only the first order of each product in the Order Details table is added to the ToDateSales column.
USE Northwind UPDATE Products SET ToDateSales = ToDateSales + od.Quantity FROM Products INNER JOIN [Order Details] od ON Products.ProductID = od.ProductID
Using Subqueries
When you use subqueries to update rows, consider the following facts and
guidelines:
SQL Server executes the subquery once for each row in the table to be updated. Therefore, the subquery must return a single value for each row.
If the subquery does not return a single value, the results are unspecified.
Consider using aggregate functions with subqueries because SQL Server never updates the same row twice in a single UPDATE statement.
Example 2
This example solves the problem of Example 1, where only the first order of each product in the Products table was updated. Many orders for each product may exist. Because SQL Server never updates the same row twice, you can use an aggregate function with a correlated subquery to update the total number of sales-to-date of each product.
USE Northwind
UPDATE Products
SET ToDateSales = (SELECT SUM(Quantity)
FROM [Order Details] od
WHERE Products.ProductID = od.ProductID)
To update rows in a table based on criteria in other tables
In this exercise, you will create a query that updates rows in the copy table to set the on_loan column to Y for those books that are represented by a copy_no/isbn that are on loan. Because the loan table contains a list of all copy_no/isbn pairs that are on loan, it is referenced to update the copy table.
Review and execute the c:\sqlimpl\exercise\ch09\updsetup.sql script, which adds a new title and copy of a book to the library database. At this point, the copy is not on loan.
Review and execute the c:\sqlimpl\exercise\ch09\loan_out.sql script, which places this copy on loan. Notice that this script updates the loan table but does not update the copy table.
Write and execute a SELECT statement that joins the copy and loan tables and returns all matching rows. Use a WHERE clause to limit the rows to those for which the copy.on_loan is not Y.
Be aware that both tables have a compound primary key, so you must reference both columns in the JOIN clause. The c:\sqlimpl\exercise\ch09\upd_cpy1.sql script file contains a completed script for this step.
USE library SELECT * FROM copy JOIN loan ON copy.isbn = loan.isbn AND copy.copy_no = loan.copy_no WHERE copy.on_loan <> 'Y'
Write and execute an UPDATE statement that uses the FROM and JOIN clauses of the SELECT statement that you created in step 3 to update the copy.on_loan column to Y for all copies that are on loan but do not have a copy.on_loan value of Y.
The c:\sqlimpl\exercise\ch09\upd_cpy2.sql script file contains a completed script for this step.
USE library UPDATE copy SET on_loan = 'Y' FROM copy JOIN loan ON copy.isbn = loan.isbn AND copy.copy_no = loan.copy_no WHERE copy.on_loan <> 'Y'
Execute the query.
Verify that the copy.on_loan column was changed from N to Y for copy_no = 1 and isbn = 10005. The c:\sqlimpl\exercise\ch09\upd_test.sql script file is an example of this query.
Lesson Summary
The INSERT, UPDATE, and DELETE statements all support advanced options for modifying data in a table based on the data from other tables.
The INSERT…SELECT statement allows you to insert data into an existing table from tables in the same or other databases.
Subqueries can be used in the SET clause of the UPDATE statement to calculate column values. Subqueries can also be used in the WHERE clause of the UPDATE and DELETE statements to specify the criteria for which rows should be updated or deleted.
The FROM clause of the UPDATE and DELETE statements is used to join other tables to the table being modified. The joined table(s) are then available for specifying the criteria for which rows should be updated or deleted.
Review
The following questions are intended to reinforce key information presented in this chapter. If you are unable to answer a question, review the appropriate lesson and then try the question again. Answers to the questions can be found in Appendix A, "Questions and Answers," located at the back of the book.
The Duluth Mutual Life health care organization has a database that tracks information about doctors and their patients. The database includes the following tables:
Doctors table
Column |
Data type and constraints |
|---|---|
doc_id |
Char(9), PRIMARY KEY |
fname |
Char(20) |
lname |
Char(25) |
specialty |
Char(25) |
phone |
Char(10) |
Patients table
Column |
Data type and constraints |
|---|---|
pat_id |
Char(9), PRIMARY KEY |
fname |
Char(20) |
lname |
Char(25) |
insurance_company |
Char(25) |
phone |
Char(10) |
Cases table
Column |
Data type and constraints |
|---|---|
admission_date |
Datetime, PRIMARY KEY (composite) |
pat_id |
Char(9), PRIMARY KEY (composite), FOREIGN KEY to patient.pat_id |
Doc_id |
Char(9), FOREIGN KEY to doctor.doc_id |
Diagnosis |
Varchar(150) |
Based on this table structure, answer the following questions:
How, with a single query, can you produce a list of all cases that were admitted on the first chronological date in the database?
You want to know the total number of hospital admissions, listed by patient name. How can you determine this? What are the advantages or disadvantages of your method?
You have numerous, user-defined databases that you administer. On a periodic basis, you want to be able to determine the amount of space that each table uses in a specified database. You plan to execute the sp_spaceused stored procedure against every table in the specified database.
Could you use a Transact-SQL cursor for the task?
Could you do this task without using a Transact-SQL cursor?
Which solution is most appropriate?
Does your answer depend on the number of tables or size of the database? For example, if you increase the number of tables or the size of the database, will your answer change?
The above article is courtesy of Microsoft Press. Copyright 1999, Microsoft Corporation.
We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages. All prices for products mentioned in this document are subject to change without notice.
International rights = English only.
.gif "Link")